TextBox Click Event

[donwb]

Win XP, Excel2003

I have a UserForm populated with many TextBoxes.
Is there an event or trigger which is fired when ANY of
the TextBoxes is clicked apart, of course, from the box itself?
I could do it by writing suitable code for each box,
but am trying to avoid this as there are too many.
donwb

 

[Dave Peterson]

John Walkenbach explains how to do this using a class module:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure/

You'll want to insert a class module in the workbook's project
(Insert|class module in the VBE).

It'll be named Class1 (unless you rename it or add more).

I used Class1 in this example.

This code goes into the class module:

Option Explicit
Public WithEvents TBoxGroup As MSForms.TextBox
Private Sub TBoxGroup_Change()
MsgBox TBoxGroup.Name & vbLf & "changed"
End Sub
Private Sub TBoxGroup_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Button = xlSecondaryButton Then
MsgBox TBoxGroup.Name & vbLf & "had the rightclick button pressed"
Else
MsgBox TBoxGroup.Name & vbLf & "not the rightclick button"
End If
End Sub

Then inside the userform module:

Option Explicit
Dim TBoxes() As New Class1
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim ctl As Control
Dim TBoxCtr As Long

TBoxCtr = 0
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then
TBoxCtr = TBoxCtr + 1
ReDim Preserve TBoxes(1 To TBoxCtr)
Set TBoxes(TBoxCtr).TBoxGroup = ctl
End If
Next ctl

End Sub


Then show the userform and test it out.