Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Is it possible to group Excel form controls with the Change event ?

Hi All,

I have an Excel VBA user form with a multi-page control (of 4 pages) each containing 6 text box controls. All 24 text boxes will contain a person's name.

If a text box value changes I want to call a small procedure to check the value of that text box. My code looks like this....

Private Sub teDelegateName1_1_Change()
   x = 1
   Call CountNoOfDelegates(x)
End Sub

Private Sub teDelegateName1_2_Change()
   x = 1
   Call CountNoOfDelegates(x)
End Sub

.........the above procedure is repeated for all 24 text fields

Private Sub CountNoOfDelegates(x)
   Dim cnt As Long
   cnt = 0
   For i = 1 To 6
      If Me.Controls("teDelegateName" & x & "_" & i) <> "" Then
         cnt = cnt + 1
      End If
   Next i
End Sub

Is there anyway I can reduce the amount of code rather than having to set up a separate change procedure for each of the text box controls ?

Thanks
Toco
0
Tocogroup
Asked:
Tocogroup
  • 4
  • 4
1 Solution
 
Rgonzo1971Commented:
Hi,

In your user form insert this code

Dim myTextBx() As clsTextBx

Private Sub UserForm_Initialize()
    Dim oneControl As Object, pointer As Long
    ReDim myTextBx(1 To Me.Controls.Count)
    For Each ControlItem In Me.Controls
    a = TypeName(ControlItem)
        If TypeName(ControlItem) = "TextBox" Then
            pointer = pointer + 1
            Set myTextBx(pointer) = New clsTextBx
            Set myTextBx(pointer).aTextBox = ControlItem
        End If
    Next ControlItem
    ReDim Preserve myTextBx(1 To pointer)
End Sub

Open in new window


and in a class module named clsTextBx insert

this code
Public WithEvents aTextBox  As msforms.TextBox

Private Sub aTextBox_Change()
   MsgBox aTextBox.Name & " was changed."
End Sub

Open in new window

Regards
0
 
TocogroupAuthor Commented:
Hi,
I inserted your code as directed. However, I get a 'User-defined type not defined' compilation error.
I'm quite ignorant of class modules. Do I have to rename the Class 1 module to match the 'clsTextBx' name declared in the user form ? If so, how do I rename it ?
0
 
Rgonzo1971Commented:
Hi,

Click the Class Module 1
Click the properties window  and change (Name)

Regards
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TocogroupAuthor Commented:
Sorry about this but it's now complaining about the ControlItem. 'Variable not defined'

Is this an Excel system variable ? Or is it a variable which needs to be declared ? If so, is it an object ?
0
 
Rgonzo1971Commented:
HI


Corrected
Dim myTextBx() As clsTextBx

Private Sub UserForm_Initialize()
    Dim ControlItem As Object, pointer As Long
    ReDim myTextBx(1 To Me.Controls.Count)
    For Each ControlItem In Me.Controls
        If TypeName(ControlItem) = "TextBox" Then
            pointer = pointer + 1
            Set myTextBx(pointer) = New clsTextBx
            Set myTextBx(pointer).aTextBox = ControlItem
        End If
    Next ControlItem
    ReDim Preserve myTextBx(1 To pointer)
End Sub 

Open in new window

Regards
0
 
TocogroupAuthor Commented:
Ok thanks.
I can now get your message to display each time I change a character in any text box on any of the pages of my Multi-page control. How do I differentiate between those Delegate Name text boxes and other text boxes ?

What I'm trying to do is count the number of Delegate names entered on a page and then display that count in a Total No of Delegates textbox. So if the user enters a Delegate's name in a textbox then the Total automatically increments by one.

Should I check for a specific textbox name in the class procedure ? This is what I have so far but it doesn't work. Far from it, in fact.

[embed=snippet 8258611]
Private Sub aTextBox_Change()

   Dim cnt As Long
   Dim i As Long
   
   On Error Resume Next
   
   ' MsgBox aTextBox.Name & " was changed."
   
   cnt = 0
   
   For i = 1 To 6
      If Me.Controls("teDelegateName" & x & "_" & i) <> "" Then
         cnt = cnt + 1
      End If
   Next i
   
   If cnt > 0 Then
      Me.Controls("teNoOfDelegates" & x) = cnt
   Else
      Me.Controls("teNoOfDelegates" & x) = ""
   End If

End Sub

Open in new window

Private Sub aTextBox_Change()

   Dim cnt As Long
   Dim i As Long
   
   On Error Resume Next
   
   ' MsgBox aTextBox.Name & " was changed."
   
   cnt = 0
   
   For i = 1 To 6
      If Me.Controls("teDelegateName" & x & "_" & i) <> "" Then
         cnt = cnt + 1
      End If
   Next i
   
   If cnt > 0 Then
      Me.Controls("teNoOfDelegates" & x) = cnt
   Else
      Me.Controls("teNoOfDelegates" & x) = ""
   End If

End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

put this code in the user form (do not forget to define x)

Sub TextBxCountDelegates()
   cnt = 0
   x = 1 ' is that what you want
   For i = 1 To 6
      If Me.Controls("teDelegateName" & x & "_" & i) <> "" Then
         cnt = cnt + 1
      End If
   Next i
   
   If cnt > 0 Then
      Me.Controls("teNoOfDelegates" & x) = cnt
   Else
      Me.Controls("teNoOfDelegates" & x) = ""
   End If

End Sub

Open in new window

in the class module ( Change Userform name)
Public WithEvents aTextBox  As msforms.TextBox

Private Sub aTextBox_Change()
If aTextBox.Name Like "teDelegateName*" Then
   Call UserForm1.TextBxCountDelegates
End If

End Sub

Open in new window


EDIT
with Delegate "Group"
Public WithEvents aTextBox  As msforms.TextBox

Private Sub aTextBox_Change()
Dim Idx As Integer
If aTextBox.Name Like "teDelegateName*" Then
    Idx = CInt(Mid(aTextBox.Name, 15, 1)) ' Which group?
    Call UserForm1.TextBxCountDelegates(Idx)
End If

End Sub

Open in new window

Sub TextBxCountDelegates(Idx As Integer)
   cnt = 0
   x = Idx
   For i = 1 To 6
      If Me.Controls("teDelegateName" & x & "_" & i) <> "" Then
         cnt = cnt + 1
      End If
   Next i
   Me.Controls("teNoOfDelegates" & x) = IIf(cnt > 0, cnt, "")
End Sub

Open in new window


Regards
0
 
TocogroupAuthor Commented:
That's really very good. I've learnt loads from this exercise.
Thank you for your time and patience.
Regards
Toco
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now