Solved

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

Posted on 2013-12-05
8
242 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 50

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 50

Expert Comment

by:Rgonzo1971
ID: 39697894
Hi,

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

Regards
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

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
 
LVL 50

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 50

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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