Array of Controls

[Kicker]

I have 10 textboxes on a userform. In VB6, they would be in an array such as txtName(index). By clicking any of the controls, I would use the index to get a value.

strValueNeeded = txtName(index).value

In vba, I am finding I need to have 10 separate subs such as

sub txtName0_click()
sub txtName1_click()
etc.

any way to use an index or array in vba?

I would prefer not to have to duplicate the code 10 times, or in reality, 40+ times in my code.

[malik641]

Not sure about the array part, but I do have one suggestion.

For every Sub txtName#_Click that you have, you can place the Call keyword in it to activate another procedure (this procedure would have your original code for the text boxes).


For example:
Private Sub txtName0_Click()
Call MyCode(Argument1, Argument2)
End Sub


Just place the Call command to each txtName# sub.

Sorry I can't answer your array question. That does sound like a lot less code, even instead of what I wrote.

[Bob Phillips]

Here is some code, but be warned, not all events are exposed in this method. For instance, there is no AfterUpdate event.

In the form code module



Option Explicit

Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cTBEvents As clsUserFormEvents
Dim ctl As msforms.Control
Set mcolEvents = New Collection
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
Set cTBEvents = New clsUserFormEvents
Set cTBEvents.mTBGroup = ctl
mcolEvents.Add cTBEvents
End If
Next
End Sub


Create a class module, call it clsUserFormEvents, and add this code




Option Explicit

Public WithEvents mTBGroup As msforms.TextBox

Private Sub mTBGroup_Change()
MsgBox mTBGroup.Text & " has been pressed"
End Sub

[Kicker]

xld

I appreciate that. After I modified it a little bit to fit my userform, it works like a champ.