Hi Dave
Thanks for the code.
I see the principle, but can't get it to run.
I loaded it as per your instructions.
It hangs at:-
ReDim Preserve TBoxes(1 To TBoxCtr)
with the error message "variable not defined".
I note that the only definition reference to TBoxes is
Dim TBoxes() As New Class1
which is outside the Userform Initialize Subroutine.
What am I missing here.
donwb


"Dave Peterson" wrote in message
...
John Walkenbach explains how to do this using a class module:
http://spreadsheetpage.com/index.php...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.





donwb wrote:

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

Either the tboxctr is incorrectly declared or the tboxes is.

Try it in a brand new workbook. Maybe the code was commingled with your
existing code and you didn't put it in the right spot????



donwb wrote:

Hi Dave
Thanks for the code.
I see the principle, but can't get it to run.
I loaded it as per your instructions.
It hangs at:-
ReDim Preserve TBoxes(1 To TBoxCtr)
with the error message "variable not defined".
I note that the only definition reference to TBoxes is
Dim TBoxes() As New Class1
which is outside the Userform Initialize Subroutine.
What am I missing here.
donwb

"Dave Peterson" wrote in message
...
John Walkenbach explains how to do this using a class module:
http://spreadsheetpage.com/index.php...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.





donwb wrote:

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

--

Dave Peterson