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
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
That's really very good. I've learnt loads from this exercise.
Thank you for your time and patience.
Regards
Toco
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.