Solved

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

Posted on 2013-12-05
8
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 52

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 52

Expert Comment

by:Rgonzo1971
ID: 39697894
Hi,

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

Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 52

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 52

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

630 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