Solved

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

Posted on 2013-12-05
8
225 Views
Last Modified: 2013-12-05
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
Comment
Question by:Tocogroup
  • 4
  • 4
8 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39697713
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
 

Author Comment

by:Tocogroup
ID: 39697831
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39697894
Hi,

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

Regards
0
 

Author Comment

by:Tocogroup
ID: 39697962
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39698028
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
 

Author Comment

by:Tocogroup
ID: 39698112
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
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39698154
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
 

Author Closing Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel ODBC connection error 3 31
Question to Pivot table 1 33
Excel VBA - Get files in a folder 5 78
Excel partial font macro 14 25
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now