I tried to modify John Walkenbach's example in the link below (i.e. assigning
one subroutine to multiple buttons) to do the same task but for textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a subroutine to 10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But my
attempt below seems to assign the subroutine to txtCatch1PreY1 only. Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

When I ran your macro, the control and the counter got "out of synch", and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way to do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a subroutine to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But my
attempt below seems to assign the subroutine to txtCatch1PreY1 only. Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

you can try this, although i've renamed thing for simplicity on my end

Option Explicit
Public WithEvents textboxGroup As MSForms.TextBox

Private Sub textboxgroup_Change()
MsgBox "Hello from " & textboxGroup.Name
End Sub


================================

Option Explicit
Dim textboxes() As New Class1
Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Long, X As Long
TextboxCount = 0
X = 1
For Each ctrl In UserForm1.Controls
If X = 11 Then Exit For
If TypeName(ctrl) = "TextBox" Then
'If ctrl.Name < "OKButton" Then 'Skip the OKButton
TextboxCount = TextboxCount + 1
ReDim Preserve textboxes(1 To TextboxCount)
Set textboxes(TextboxCount).textboxGroup = ctrl
'End If
End If
X = X + 1
Next
UserForm1.Show
End Sub


--


Gary


"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e. assigning
one subroutine to multiple buttons) to do the same task but for textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a subroutine to 10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But my
attempt below seems to assign the subroutine to txtCatch1PreY1 only. Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also a number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of synch", and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way to do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a subroutine to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But my
attempt below seems to assign the subroutine to txtCatch1PreY1 only. Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

What's Z supposed to do? Your addition would make X loop 10 times for each
Z, or 100 times total and create multiple instances of the control event
reference.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more
textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also a
number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of synch",
and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way to
do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for
textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a subroutine
to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But my
attempt below seems to assign the subroutine to txtCatch1PreY1 only.
Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

Thanks for your reply.
Yes, I've tried to assign the subroutine to 100 textboxes, named in the
format of txtCatchZPreYX (e.g. txtCatch1PreY1, ..., txtCatch1PreY10,
....txtCatch10PreY1, ..., txtCatch10PreY10). And would like to be able call
out the Z value associated with each textbox, if it's doable?



"Tim Zych" wrote:

What's Z supposed to do? Your addition would make X loop 10 times for each
Z, or 100 times total and create multiple instances of the control event
reference.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more
textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also a
number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of synch",
and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way to
do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for
textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a subroutine
to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But my
attempt below seems to assign the subroutine to txtCatch1PreY1 only.
Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

Ok I get what you want to do now.

This has a new property called "ParamValue". When the object is created,
pass it X so the textbox knows which value to display.

' In the module
Dim TextBoxes() As New Class1
Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
TextBoxes(TextboxCount).ParamValue = X ' < --------- this is
new
End If
Next X
UserForm1.Show
End Sub

' In Class1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox
'' ---------------------------
' This is new
' ---------------------------
Private mParamValue As Long
Property Let ParamValue(Value As Long)
mParamValue = Value
End Property
Property Get ParamValue() As Long
ParamValue = mParamValue
End Property
' ---------------------------
Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name & vbLf & _
"Parameter value = " & Me.ParamValue ' < ---------- this is new
End Sub

HTH,

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Sam Kuo" wrote in message
...
Thanks for your reply.
Yes, I've tried to assign the subroutine to 100 textboxes, named in the
format of txtCatchZPreYX (e.g. txtCatch1PreY1, ..., txtCatch1PreY10,
...txtCatch10PreY1, ..., txtCatch10PreY10). And would like to be able call
out the Z value associated with each textbox, if it's doable?



"Tim Zych" wrote:

What's Z supposed to do? Your addition would make X loop 10 times for
each
Z, or 100 times total and create multiple instances of the control event
reference.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more
textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also a
number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I
can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of synch",
and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way
to
do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for
textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a
subroutine
to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But
my
attempt below seems to assign the subroutine to txtCatch1PreY1 only.
Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

That's awesome! Thanks

Sam


"Tim Zych" wrote:

Ok I get what you want to do now.

This has a new property called "ParamValue". When the object is created,
pass it X so the textbox knows which value to display.

' In the module
Dim TextBoxes() As New Class1
Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
TextBoxes(TextboxCount).ParamValue = X ' < --------- this is
new
End If
Next X
UserForm1.Show
End Sub

' In Class1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox
'' ---------------------------
' This is new
' ---------------------------
Private mParamValue As Long
Property Let ParamValue(Value As Long)
mParamValue = Value
End Property
Property Get ParamValue() As Long
ParamValue = mParamValue
End Property
' ---------------------------
Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name & vbLf & _
"Parameter value = " & Me.ParamValue ' < ---------- this is new
End Sub

HTH,

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Sam Kuo" wrote in message
...
Thanks for your reply.
Yes, I've tried to assign the subroutine to 100 textboxes, named in the
format of txtCatchZPreYX (e.g. txtCatch1PreY1, ..., txtCatch1PreY10,
...txtCatch10PreY1, ..., txtCatch10PreY10). And would like to be able call
out the Z value associated with each textbox, if it's doable?



"Tim Zych" wrote:

What's Z supposed to do? Your addition would make X loop 10 times for
each
Z, or 100 times total and create multiple instances of the control event
reference.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more
textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also a
number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I
can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of synch",
and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way
to
do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for
textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a
subroutine
to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But
my
attempt below seems to assign the subroutine to txtCatch1PreY1 only.
Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

mine didn't work?
--


Gary


"Sam Kuo" wrote in message
...
That's awesome! Thanks

Sam


"Tim Zych" wrote:

Ok I get what you want to do now.

This has a new property called "ParamValue". When the object is created,
pass it X so the textbox knows which value to display.

' In the module
Dim TextBoxes() As New Class1
Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
TextBoxes(TextboxCount).ParamValue = X ' < --------- this is
new
End If
Next X
UserForm1.Show
End Sub

' In Class1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox
'' ---------------------------
' This is new
' ---------------------------
Private mParamValue As Long
Property Let ParamValue(Value As Long)
mParamValue = Value
End Property
Property Get ParamValue() As Long
ParamValue = mParamValue
End Property
' ---------------------------
Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name & vbLf & _
"Parameter value = " & Me.ParamValue ' < ---------- this is new
End Sub

HTH,

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Sam Kuo" wrote in message
...
Thanks for your reply.
Yes, I've tried to assign the subroutine to 100 textboxes, named in the
format of txtCatchZPreYX (e.g. txtCatch1PreY1, ..., txtCatch1PreY10,
...txtCatch10PreY1, ..., txtCatch10PreY10). And would like to be able call
out the Z value associated with each textbox, if it's doable?



"Tim Zych" wrote:

What's Z supposed to do? Your addition would make X loop 10 times for
each
Z, or 100 times total and create multiple instances of the control event
reference.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more
textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also a
number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I
can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of synch",
and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way
to
do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for
textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a
subroutine
to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But
my
attempt below seems to assign the subroutine to txtCatch1PreY1 only.
Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

Thanks Gary. It's great :-)

"Gary Keramidas" wrote:

you can try this, although i've renamed thing for simplicity on my end

Option Explicit
Public WithEvents textboxGroup As MSForms.TextBox

Private Sub textboxgroup_Change()
MsgBox "Hello from " & textboxGroup.Name
End Sub


================================

Option Explicit
Dim textboxes() As New Class1
Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Long, X As Long
TextboxCount = 0
X = 1
For Each ctrl In UserForm1.Controls
If X = 11 Then Exit For
If TypeName(ctrl) = "TextBox" Then
'If ctrl.Name < "OKButton" Then 'Skip the OKButton
TextboxCount = TextboxCount + 1
ReDim Preserve textboxes(1 To TextboxCount)
Set textboxes(TextboxCount).textboxGroup = ctrl
'End If
End If
X = X + 1
Next
UserForm1.Show
End Sub


--


Gary


"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e. assigning
one subroutine to multiple buttons) to do the same task but for textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a subroutine to 10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But my
attempt below seems to assign the subroutine to txtCatch1PreY1 only. Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

Hi Tim
One more question if could be a pain...

Below is my finished code, which supposedly should assign one subroutine to
all 600 textboxes. Everything is the same as before, except here I tried to
add two extra loops (i.e. "Stage" and "Axis") to accommodate more textboxes.

But the problem in my attempt below is that it seems to loop through only
the first loop of both Stage and Axis (i.e. it only assigns the subroutine to
those textboxes named with Stage = Pre and Axis = X). What have I done wrong
here?

Your kind help would be much appreciated!


Option Explicit
Dim TextBoxes() As New Class1

Sub ShowDialog()
' Assign a subroutine to 600 textboxes (named in the format of
"txtCatch" &
' CatchmentNo & Stage & Axis & PointNo) with Change event.
Dim ctrl As Control
Dim TextboxCount As Integer
Dim CatchmentNo As Integer '= 1 to 10
Dim Stage As Variant '= Pre, Earthwks or Post
Dim Axis As Variant '= X or Y
Dim PointNo As Integer '= 1 to 10

TextboxCount = 0
For CatchmentNo = 1 To 10 Step 1
For Each Stage In Split("Pre, Earthwks, Post", ",") ' this is new
For Each Axis In Split("X, Y", ",") ' this is new
For PointNo = 1 To 10 Step 1
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch" & CatchmentNo &
Stage & Axis & PointNo) ' this is changed
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
TextBoxes(TextboxCount).CatchmentNoValue = CatchmentNo
TextBoxes(TextboxCount).StageValue = Stage ' this
is new
TextBoxes(TextboxCount).AxisValue = Axis ' this is
new
TextBoxes(TextboxCount).PointNoValue = PointNo
End If
Next PointNo
Next Axis ' this is new
Next Stage ' this is new
Next CatchmentNo
UserForm1.Show
End Sub


Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private mCatchmentNoValue As Long
Private mStageValue As Variant ' this is new
Private mAxisValue As Variant ' this is new
Private mPointNoValue As Long

Property Let CatchmentNoValue(Value As Long)
mCatchmentNoValue = Value
End Property
Property Get CatchmentNoValue() As Long
CatchmentNoValue = mCatchmentNoValue
End Property

' below is new
Property Let StageValue(Value As Variant)
mStageValue = Value
End Property
Property Get StageValue() As Variant
StageValue = mStageValue
End Property

Property Let AxisValue(Value As Variant)
mAxisValue = Value
End Property
Property Get AxisValue() As Variant
AxisValue = mAxisValue
End Property
' above is new

Property Let PointNoValue(Value As Long)
mPointNoValue = Value
End Property
Property Get PointNoValue() As Long
PointNoValue = mPointNoValue
End Property

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name & vbLf & _
"CatchmentNo value = " & Me.CatchmentNoValue & vbLf & _
"Stage value = " & Me.StageValue & vbLf & _ ' this is new
"Axis value = " & Me.AxisValue & vbLf & _ ' this is new
"PointNo value = " & Me.PointNoValue
End Sub


"Tim Zych" wrote:

Ok I get what you want to do now.

This has a new property called "ParamValue". When the object is created,
pass it X so the textbox knows which value to display.

' In the module
Dim TextBoxes() As New Class1
Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
TextBoxes(TextboxCount).ParamValue = X ' < --------- this is
new
End If
Next X
UserForm1.Show
End Sub

' In Class1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox
'' ---------------------------
' This is new
' ---------------------------
Private mParamValue As Long
Property Let ParamValue(Value As Long)
mParamValue = Value
End Property
Property Get ParamValue() As Long
ParamValue = mParamValue
End Property
' ---------------------------
Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name & vbLf & _
"Parameter value = " & Me.ParamValue ' < ---------- this is new
End Sub

HTH,

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Sam Kuo" wrote in message
...
Thanks for your reply.
Yes, I've tried to assign the subroutine to 100 textboxes, named in the
format of txtCatchZPreYX (e.g. txtCatch1PreY1, ..., txtCatch1PreY10,
...txtCatch10PreY1, ..., txtCatch10PreY10). And would like to be able call
out the Z value associated with each textbox, if it's doable?



"Tim Zych" wrote:

What's Z supposed to do? Your addition would make X loop 10 times for
each
Z, or 100 times total and create multiple instances of the control event
reference.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more
textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also a
number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I
can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of synch",
and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another way
to
do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below (i.e.
assigning
one subroutine to multiple buttons) to do the same task but for
textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a
subroutine
to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10). But
my
attempt below seems to assign the subroutine to txtCatch1PreY1 only.
Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub

Probably the Split statements, and the spaces that are included when the
items are split

For Each Stage In Split("Pre, Earthwks, Post", ",")
For Each Axis In Split("X, Y", ",")

should be

For Each Stage In Split("Pre,Earthwks,Post", ",")
For Each Axis In Split("X,Y", ",")

(or use the Trim function for each item)

otherwise you will get names like

txtCatch7 Earthwks Y4

instead of

txtCatch7EarthwksY4

but this raises a different issue since you have so many textboxes, how did
they get there. If programmatically creating the textboxes, you could
alternatively attach the textbox as it's created rather than cycling through
after the fact based on name (if possible), e.g.:

Set ctrl = UserForm1.Controls.Add("Forms.TextBox.1", "")
' CurrTop = CurrTop + ctrl.Top + ctrl.Height
' ctrl.Top = CurrTop
' ...
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl


--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Sam Kuo" wrote in message
...
Hi Tim
One more question if could be a pain...

Below is my finished code, which supposedly should assign one subroutine
to
all 600 textboxes. Everything is the same as before, except here I tried
to
add two extra loops (i.e. "Stage" and "Axis") to accommodate more
textboxes.

But the problem in my attempt below is that it seems to loop through only
the first loop of both Stage and Axis (i.e. it only assigns the subroutine
to
those textboxes named with Stage = Pre and Axis = X). What have I done
wrong
here?

Your kind help would be much appreciated!


Option Explicit
Dim TextBoxes() As New Class1

Sub ShowDialog()
' Assign a subroutine to 600 textboxes (named in the format of
"txtCatch" &
' CatchmentNo & Stage & Axis & PointNo) with Change event.
Dim ctrl As Control
Dim TextboxCount As Integer
Dim CatchmentNo As Integer '= 1 to 10
Dim Stage As Variant '= Pre, Earthwks or Post
Dim Axis As Variant '= X or Y
Dim PointNo As Integer '= 1 to 10

TextboxCount = 0
For CatchmentNo = 1 To 10 Step 1
For Each Stage In Split("Pre, Earthwks, Post", ",") ' this is new
For Each Axis In Split("X, Y", ",") ' this is new
For PointNo = 1 To 10 Step 1
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch" & CatchmentNo
&
Stage & Axis & PointNo) ' this is changed
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup =
ctrl
TextBoxes(TextboxCount).CatchmentNoValue =
CatchmentNo
TextBoxes(TextboxCount).StageValue = Stage ' this
is new
TextBoxes(TextboxCount).AxisValue = Axis ' this
is
new
TextBoxes(TextboxCount).PointNoValue = PointNo
End If
Next PointNo
Next Axis ' this is new
Next Stage ' this is new
Next CatchmentNo
UserForm1.Show
End Sub


Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private mCatchmentNoValue As Long
Private mStageValue As Variant ' this is new
Private mAxisValue As Variant ' this is new
Private mPointNoValue As Long

Property Let CatchmentNoValue(Value As Long)
mCatchmentNoValue = Value
End Property
Property Get CatchmentNoValue() As Long
CatchmentNoValue = mCatchmentNoValue
End Property

' below is new
Property Let StageValue(Value As Variant)
mStageValue = Value
End Property
Property Get StageValue() As Variant
StageValue = mStageValue
End Property

Property Let AxisValue(Value As Variant)
mAxisValue = Value
End Property
Property Get AxisValue() As Variant
AxisValue = mAxisValue
End Property
' above is new

Property Let PointNoValue(Value As Long)
mPointNoValue = Value
End Property
Property Get PointNoValue() As Long
PointNoValue = mPointNoValue
End Property

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name & vbLf & _
"CatchmentNo value = " & Me.CatchmentNoValue & vbLf & _
"Stage value = " & Me.StageValue & vbLf & _ ' this is new
"Axis value = " & Me.AxisValue & vbLf & _ ' this is new
"PointNo value = " & Me.PointNoValue
End Sub


"Tim Zych" wrote:

Ok I get what you want to do now.

This has a new property called "ParamValue". When the object is created,
pass it X so the textbox knows which value to display.

' In the module
Dim TextBoxes() As New Class1
Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
TextBoxes(TextboxCount).ParamValue = X ' < --------- this
is
new
End If
Next X
UserForm1.Show
End Sub

' In Class1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox
'' ---------------------------
' This is new
' ---------------------------
Private mParamValue As Long
Property Let ParamValue(Value As Long)
mParamValue = Value
End Property
Property Get ParamValue() As Long
ParamValue = mParamValue
End Property
' ---------------------------
Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name & vbLf & _
"Parameter value = " & Me.ParamValue ' < ---------- this is
new
End Sub

HTH,

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Sam Kuo" wrote in message
...
Thanks for your reply.
Yes, I've tried to assign the subroutine to 100 textboxes, named in the
format of txtCatchZPreYX (e.g. txtCatch1PreY1, ..., txtCatch1PreY10,
...txtCatch10PreY1, ..., txtCatch10PreY10). And would like to be able
call
out the Z value associated with each textbox, if it's doable?



"Tim Zych" wrote:

What's Z supposed to do? Your addition would make X loop 10 times for
each
Z, or 100 times total and create multiple instances of the control
event
reference.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
Hi Tim, thanks for your swift reply.

I've just added another Z loop outside the X loop to cater for more
textboxes
(i.e. txtCatchZPreYX, where Z is a number of 1 to 10, and X is also
a
number
of 1 to 10) and it still works great.

Is there a way to "preserve" the Z value for each textbox, so that I
can
call it out in the textbox_change subroutine?

Below is what I've tried, but obviously the Z value is not stored
and
returns as nothing when execute the subroutine...

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
' I tried adding another Z loop
For Z = 1 To 10
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next X
Next Z
UserForm1.Show
End Sub

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
' But Z value is not stored....
MsgBox Z
End Sub



"Tim Zych" wrote:

When I ran your macro, the control and the counter got "out of
synch",
and
only a couple were being set.

Using your specs of 10 textboxes named Y1...Y10, this is another
way
to
do
it.

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer, X As Integer
TextboxCount = 0
For X = 1 To 10
On Error Resume Next
Set ctrl = Nothing
Set ctrl = UserForm1.Controls("txtCatch1PreY" & X)
On Error GoTo 0
If Not ctrl Is Nothing Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup = ctrl
End If
Next
UserForm1.Show
End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook
Compare
A free, powerful, flexible Excel utility

"Sam Kuo" wrote in message
...
I tried to modify John Walkenbach's example in the link below
(i.e.
assigning
one subroutine to multiple buttons) to do the same task but for
textboxes
instead of buttons:
http://www.j-walk.com/ss/excel/tips/tip44.htm

e.g. I have several textboxes, and I only want to assign a
subroutine
to
10
particular textboxes (namely txtCatch1PreY1 to txtCatch1PreY10).
But
my
attempt below seems to assign the subroutine to txtCatch1PreY1
only.
Can
someone please help me spot where the problem is in my code?

' The following code is in Class Module 1
Public WithEvents SlopeTextboxGroup As MSForms.TextBox

Private Sub SlopeTextboxGroup_Change()
MsgBox "Hello from " & SlopeTextboxGroup.Name
End Sub


' The following code is in Module 1
Option Explicit

Dim TextBoxes() As New Class1

Sub ShowDialog()
Dim ctrl As Control
Dim TextboxCount As Integer

TextboxCount = 1
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl.Name = "txtCatch1PreY" & TextboxCount Then
TextboxCount = TextboxCount + 1
ReDim Preserve TextBoxes(1 To TextboxCount)
Set TextBoxes(TextboxCount).SlopeTextboxGroup =
ctrl
End If
End If
Next ctrl
' Show UserForm1
UserForm1.Show
End Sub