Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

Maybe you can use a loop and instead of using the Call method try the
Application.Run method?

--
Zack Barresse



"Minitman" wrote in message
...
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

Hey Zack,

Thanks for the reply.

I apologize for not making this problem clearer.

I am using the call method to start a sub (MySub) and the number
portion of TextBox99 as the argument for that sub. The "99" needs to
be some kind of variable so that as I paste the code into the other
199 TextBox subs. This will allow the code to automatically determine
what the argument should be (the TextBox number) and pass that number
on to the sub.

Any ideas on how to do this?

-Minitman



On Thu, 24 Apr 2008 16:04:57 -0700, "Zack Barresse"
wrote:

Maybe you can use a loop and instead of using the Call method try the
Application.Run method?

--
Zack Barresse



"Minitman" wrote in message
.. .
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99_Enter()
Call MySub "TextBox99"
End Sub

Sub MySub(vName As Variant)
MsgBox "You have entered " & vName
End Sub

Is there anyway to replace the text "TextBox99" with some code that
instead gets the text string from the Sub name?

Any help would be appreciated.

-Minitman

I don't understand how what I gave you is not what you're asking for.

As a side note, why would you be copy/pasting the routine to that many?!?!
What a nightmare!!! Make one routine and have each of them call it. Pass a
variable if you'd like. But variables can be passed even as sub routine
names! (The 'Application.Run' method I mentioned.)

I guess I'm not sure what exactly you are trying to do with your project. I
understand it is on a userform, but what is the full intent, and the extent
of your project?

--
Zack Barresse



"Minitman" wrote in message
...
Hey Zack,

Thanks for the reply.

I apologize for not making this problem clearer.

I am using the call method to start a sub (MySub) and the number
portion of TextBox99 as the argument for that sub. The "99" needs to
be some kind of variable so that as I paste the code into the other
199 TextBox subs. This will allow the code to automatically determine
what the argument should be (the TextBox number) and pass that number
on to the sub.

Any ideas on how to do this?

-Minitman



On Thu, 24 Apr 2008 16:04:57 -0700, "Zack Barresse"
wrote:

Maybe you can use a loop and instead of using the Call method try the
Application.Run method?

--
Zack Barresse



"Minitman" wrote in message
. ..
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

How is TextBox99 currently being called?

Tim





"Minitman" wrote in message
...
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

Hey Tim,

Thanks for the reply

It was supposed to be an "Enter" event attached to TextBox99 (not a
just a sub).

I made a couple of changes to the question (I was asking for two
things, how to break up the text string that is the name and how the
code could figure out what the name of the sub is from inside of the
sub) so I reposted this question with a slightly different name about
5 minutes before you posted this reply.

You can see the changes in the posting called:

How To USe The Name Of The TextBox In That TextBoxes Code - (Was "How
To Use The Number Part Of A TextBox Name As A Variable")

The subject is a little longer then I am used to. :^}

Again, thanks for the reply.

-Minitman




On Thu, 24 Apr 2008 17:39:06 -0700, "Tim Williams" <timjwilliams at
gmail dot com wrote:

How is TextBox99 currently being called?

Tim





"Minitman" wrote in message
.. .
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

Try this, add a class module named Class1 and a userform with some textboxes
and a button.

'''Class1 code
'
Public WithEvents tbx As MSForms.TextBox
Public gID As Long

Private Sub tbx_Change()
tbx.Parent.Caption = "TB-" & gID & ": " & tbx.Text
End Sub

Public Sub TextBoxStuff(nClr As Long)
tbx.BackColor = nClr
End Sub

''''End class1 code

''''''' Userform code
' include a Commandbutton
' and some Texboxes, change NumOfTextBoxes to suit

Private arrTBoxes() As Class1

Private Sub CommandButton1_Click()
Dim nClr As Long
Static tbID As Long
tbID = tbID + 1
If tbID UBound(arrTBoxes) Then tbID = 1
nClr = Int(Rnd() * 16777215)

arrTBoxes(tbID).TextBoxStuff nClr

End Sub

Private Sub UserForm_Initialize()
Const NumOfTextBoxes As Long = 3 ' < CHANGE

ReDim arrTBoxes(1 To NumOfTextBoxes)

Dim i As Long
For i = 1 To NumOfTextBoxes
Set arrTBoxes(i) = New Class1
With arrTBoxes(i)
Set .tbx = Me.Controls("TextBox" & i)
.gID = i
End With
Next
End Sub

If you want to refer to your textboxes and call 'TextBoxStuff' from
elsewhere in your project, move "Private arrTBoxes() As Class1" into a
normal module and change Private to Public. Most, though not all textbox
events can be included in the withevents class.

Regards,
Peter T

"Minitman" wrote in message
...
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

Hey Peter,

Thanks for the reply.

OPPS!!! I seem to have left out a few to many facts concerning my
problem. My bad!!

My problem area is on a UserForm. I have a combination of TextBoxes
and ComboBoxes all named "Input_" with numbers from 1 to 124 . These
are all either TextBoxes or ComboBoxes.

Originally I only needed to make this TextBox code more universal so
that I could just paste and copy any changes:

Private Sub Input_99_Enter()
Call MySub {Sub name text here} & {Sub name number here}
End Sub

Sub MySub(vName As Variant) 'Only a tester, to see if it is working
MsgBox "You have entered " & vName
End Sub

MySub's only function is to tell me that the code is working.

I can't see any way to get the argument in:
Call MySub {Sub name text here} & {Sub name number here}
to change with each "Input_"

Please excuse my ignorance, I appreciate the effort and the code but I
can't see how to get what I need (a variable for the argument that
returns the name of the control - hopefully split into text and
numbers). I will be playing with your code to see if I can get it to
work. I am not sure of what I am doing but I will try anyhow.

Any other ideas are also welcome?

-Minitman



On Fri, 25 Apr 2008 10:18:56 +0100, "Peter T" <peter_t@discussions
wrote:

Try this, add a class module named Class1 and a userform with some textboxes
and a button.

'''Class1 code
'
Public WithEvents tbx As MSForms.TextBox
Public gID As Long

Private Sub tbx_Change()
tbx.Parent.Caption = "TB-" & gID & ": " & tbx.Text
End Sub

Public Sub TextBoxStuff(nClr As Long)
tbx.BackColor = nClr
End Sub

''''End class1 code

''''''' Userform code
' include a Commandbutton
' and some Texboxes, change NumOfTextBoxes to suit

Private arrTBoxes() As Class1

Private Sub CommandButton1_Click()
Dim nClr As Long
Static tbID As Long
tbID = tbID + 1
If tbID UBound(arrTBoxes) Then tbID = 1
nClr = Int(Rnd() * 16777215)

arrTBoxes(tbID).TextBoxStuff nClr

End Sub

Private Sub UserForm_Initialize()
Const NumOfTextBoxes As Long = 3 ' < CHANGE

ReDim arrTBoxes(1 To NumOfTextBoxes)

Dim i As Long
For i = 1 To NumOfTextBoxes
Set arrTBoxes(i) = New Class1
With arrTBoxes(i)
Set .tbx = Me.Controls("TextBox" & i)
.gID = i
End With
Next
End Sub

If you want to refer to your textboxes and call 'TextBoxStuff' from
elsewhere in your project, move "Private arrTBoxes() As Class1" into a
normal module and change Private to Public. Most, though not all textbox
events can be included in the withevents class.

Regards,
Peter T

"Minitman" wrote in message
.. .
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

What do you actually want your "Private Sub Input_99_Enter()" to do.

If you merely want to read or write some property

me.controls("Textbox" & Num).etc

Try and follow how what I posted, I suspect it will enable whatever it is
you want to do. Unless, that is, you want to use the Withevents Class to
trap Enter, Exit, Before/After_update events, which sadly it won't with
Textboxes.

Regards,
Peter T


"Minitman" wrote in message
...
Hey Peter,

Thanks for the reply.

OPPS!!! I seem to have left out a few to many facts concerning my
problem. My bad!!

My problem area is on a UserForm. I have a combination of TextBoxes
and ComboBoxes all named "Input_" with numbers from 1 to 124 . These
are all either TextBoxes or ComboBoxes.

Originally I only needed to make this TextBox code more universal so
that I could just paste and copy any changes:

Private Sub Input_99_Enter()
Call MySub {Sub name text here} & {Sub name number here}
End Sub

Sub MySub(vName As Variant) 'Only a tester, to see if it is working
MsgBox "You have entered " & vName
End Sub

MySub's only function is to tell me that the code is working.

I can't see any way to get the argument in:
Call MySub {Sub name text here} & {Sub name number here}
to change with each "Input_"

Please excuse my ignorance, I appreciate the effort and the code but I
can't see how to get what I need (a variable for the argument that
returns the name of the control - hopefully split into text and
numbers). I will be playing with your code to see if I can get it to
work. I am not sure of what I am doing but I will try anyhow.

Any other ideas are also welcome?

-Minitman



On Fri, 25 Apr 2008 10:18:56 +0100, "Peter T" <peter_t@discussions
wrote:

Try this, add a class module named Class1 and a userform with some
textboxes
and a button.

'''Class1 code
'
Public WithEvents tbx As MSForms.TextBox
Public gID As Long

Private Sub tbx_Change()
tbx.Parent.Caption = "TB-" & gID & ": " & tbx.Text
End Sub

Public Sub TextBoxStuff(nClr As Long)
tbx.BackColor = nClr
End Sub

''''End class1 code

''''''' Userform code
' include a Commandbutton
' and some Texboxes, change NumOfTextBoxes to suit

Private arrTBoxes() As Class1

Private Sub CommandButton1_Click()
Dim nClr As Long
Static tbID As Long
tbID = tbID + 1
If tbID UBound(arrTBoxes) Then tbID = 1
nClr = Int(Rnd() * 16777215)

arrTBoxes(tbID).TextBoxStuff nClr

End Sub

Private Sub UserForm_Initialize()
Const NumOfTextBoxes As Long = 3 ' < CHANGE

ReDim arrTBoxes(1 To NumOfTextBoxes)

Dim i As Long
For i = 1 To NumOfTextBoxes
Set arrTBoxes(i) = New Class1
With arrTBoxes(i)
Set .tbx = Me.Controls("TextBox" & i)
.gID = i
End With
Next
End Sub

If you want to refer to your textboxes and call 'TextBoxStuff' from
elsewhere in your project, move "Private arrTBoxes() As Class1" into a
normal module and change Private to Public. Most, though not all textbox
events can be included in the withevents class.

Regards,
Peter T

"Minitman" wrote in message
.. .
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

I only want the name ("Input_" & "99") split into text and numbers (if
possible), to be sent to the called subroutine, in the form a
variable, as an argument.

No properties, just the sub's name. I chose the enter event only to
trigger the call to MySub. I need the name of the sub to be passed
thru as the argument for MySub.

I don't want to enable anything, only send the name of the
TextBox/ComboBox that I am in, to MySub as an argument.

I did not see where that was accomplished in your code.

However, I may not have enough experience to make that determination.

Any ideas as to how to detect the name of the TextBox sub or
explanation as to how that has already been accomplish in your
supplied code, would be appreciated.

-Minitman



On Fri, 25 Apr 2008 14:11:57 +0100, "Peter T" <peter_t@discussions
wrote:

What do you actually want your "Private Sub Input_99_Enter()" to do.

If you merely want to read or write some property

me.controls("Textbox" & Num).etc

Try and follow how what I posted, I suspect it will enable whatever it is
you want to do. Unless, that is, you want to use the Withevents Class to
trap Enter, Exit, Before/After_update events, which sadly it won't with
Textboxes.

Regards,
Peter T


"Minitman" wrote in message
.. .
Hey Peter,

Thanks for the reply.

OPPS!!! I seem to have left out a few to many facts concerning my
problem. My bad!!

My problem area is on a UserForm. I have a combination of TextBoxes
and ComboBoxes all named "Input_" with numbers from 1 to 124 . These
are all either TextBoxes or ComboBoxes.

Originally I only needed to make this TextBox code more universal so
that I could just paste and copy any changes:

Private Sub Input_99_Enter()
Call MySub {Sub name text here} & {Sub name number here}
End Sub

Sub MySub(vName As Variant) 'Only a tester, to see if it is working
MsgBox "You have entered " & vName
End Sub

MySub's only function is to tell me that the code is working.

I can't see any way to get the argument in:
Call MySub {Sub name text here} & {Sub name number here}
to change with each "Input_"

Please excuse my ignorance, I appreciate the effort and the code but I
can't see how to get what I need (a variable for the argument that
returns the name of the control - hopefully split into text and
numbers). I will be playing with your code to see if I can get it to
work. I am not sure of what I am doing but I will try anyhow.

Any other ideas are also welcome?

-Minitman



On Fri, 25 Apr 2008 10:18:56 +0100, "Peter T" <peter_t@discussions
wrote:

Try this, add a class module named Class1 and a userform with some
textboxes
and a button.

'''Class1 code
'
Public WithEvents tbx As MSForms.TextBox
Public gID As Long

Private Sub tbx_Change()
tbx.Parent.Caption = "TB-" & gID & ": " & tbx.Text
End Sub

Public Sub TextBoxStuff(nClr As Long)
tbx.BackColor = nClr
End Sub

''''End class1 code

''''''' Userform code
' include a Commandbutton
' and some Texboxes, change NumOfTextBoxes to suit

Private arrTBoxes() As Class1

Private Sub CommandButton1_Click()
Dim nClr As Long
Static tbID As Long
tbID = tbID + 1
If tbID UBound(arrTBoxes) Then tbID = 1
nClr = Int(Rnd() * 16777215)

arrTBoxes(tbID).TextBoxStuff nClr

End Sub

Private Sub UserForm_Initialize()
Const NumOfTextBoxes As Long = 3 ' < CHANGE

ReDim arrTBoxes(1 To NumOfTextBoxes)

Dim i As Long
For i = 1 To NumOfTextBoxes
Set arrTBoxes(i) = New Class1
With arrTBoxes(i)
Set .tbx = Me.Controls("TextBox" & i)
.gID = i
End With
Next
End Sub

If you want to refer to your textboxes and call 'TextBoxStuff' from
elsewhere in your project, move "Private arrTBoxes() As Class1" into a
normal module and change Private to Public. Most, though not all textbox
events can be included in the withevents class.

Regards,
Peter T

"Minitman" wrote in message
.. .
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman

"Minitman" wrote in message
...

Any ideas as to how to detect the name of the TextBox sub or
explanation as to how that has already been accomplish in your
supplied code, would be appreciated.

Well, one thing I can tell you is to abandon the idea of trying to detect
the name of the text box that triggered the event. In VBA this simply isn't
possible. This was a design problem that was corrected in .NET, but that
fact doesn't help you here. (And I'm not some .NET evangelist--I'm a VB6
guy--but I give credit where credit is due.)

I'm sure I'm missing something but for what you ask simply

Sub myProc(sArg1 as string, sArg2 as String)

Call myProc(("Input_", cStr(99))

Are you stuck on how to split "Input_99" into respective arguments

Under what circumstances (some event, user clicks a textbox, etc) do you
want to call the routine, how is "Input_99" generated, and what do you want
the routine to do (in simple terms).

Regards,
Peter T

"Minitman" wrote in message
...
I only want the name ("Input_" & "99") split into text and numbers (if
possible), to be sent to the called subroutine, in the form a
variable, as an argument.

No properties, just the sub's name. I chose the enter event only to
trigger the call to MySub. I need the name of the sub to be passed
thru as the argument for MySub.

I don't want to enable anything, only send the name of the
TextBox/ComboBox that I am in, to MySub as an argument.

I did not see where that was accomplished in your code.

However, I may not have enough experience to make that determination.

Any ideas as to how to detect the name of the TextBox sub or
explanation as to how that has already been accomplish in your
supplied code, would be appreciated.

-Minitman



On Fri, 25 Apr 2008 14:11:57 +0100, "Peter T" <peter_t@discussions
wrote:

What do you actually want your "Private Sub Input_99_Enter()" to do.

If you merely want to read or write some property

me.controls("Textbox" & Num).etc

Try and follow how what I posted, I suspect it will enable whatever it is
you want to do. Unless, that is, you want to use the Withevents Class to
trap Enter, Exit, Before/After_update events, which sadly it won't with
Textboxes.

Regards,
Peter T


"Minitman" wrote in message
.. .
Hey Peter,

Thanks for the reply.

OPPS!!! I seem to have left out a few to many facts concerning my
problem. My bad!!

My problem area is on a UserForm. I have a combination of TextBoxes
and ComboBoxes all named "Input_" with numbers from 1 to 124 . These
are all either TextBoxes or ComboBoxes.

Originally I only needed to make this TextBox code more universal so
that I could just paste and copy any changes:

Private Sub Input_99_Enter()
Call MySub {Sub name text here} & {Sub name number here}
End Sub

Sub MySub(vName As Variant) 'Only a tester, to see if it is working
MsgBox "You have entered " & vName
End Sub

MySub's only function is to tell me that the code is working.

I can't see any way to get the argument in:
Call MySub {Sub name text here} & {Sub name number here}
to change with each "Input_"

Please excuse my ignorance, I appreciate the effort and the code but I
can't see how to get what I need (a variable for the argument that
returns the name of the control - hopefully split into text and
numbers). I will be playing with your code to see if I can get it to
work. I am not sure of what I am doing but I will try anyhow.

Any other ideas are also welcome?

-Minitman



On Fri, 25 Apr 2008 10:18:56 +0100, "Peter T" <peter_t@discussions
wrote:

Try this, add a class module named Class1 and a userform with some
textboxes
and a button.

'''Class1 code
'
Public WithEvents tbx As MSForms.TextBox
Public gID As Long

Private Sub tbx_Change()
tbx.Parent.Caption = "TB-" & gID & ": " & tbx.Text
End Sub

Public Sub TextBoxStuff(nClr As Long)
tbx.BackColor = nClr
End Sub

''''End class1 code

''''''' Userform code
' include a Commandbutton
' and some Texboxes, change NumOfTextBoxes to suit

Private arrTBoxes() As Class1

Private Sub CommandButton1_Click()
Dim nClr As Long
Static tbID As Long
tbID = tbID + 1
If tbID UBound(arrTBoxes) Then tbID = 1
nClr = Int(Rnd() * 16777215)

arrTBoxes(tbID).TextBoxStuff nClr

End Sub

Private Sub UserForm_Initialize()
Const NumOfTextBoxes As Long = 3 ' < CHANGE

ReDim arrTBoxes(1 To NumOfTextBoxes)

Dim i As Long
For i = 1 To NumOfTextBoxes
Set arrTBoxes(i) = New Class1
With arrTBoxes(i)
Set .tbx = Me.Controls("TextBox" & i)
.gID = i
End With
Next
End Sub

If you want to refer to your textboxes and call 'TextBoxStuff' from
elsewhere in your project, move "Private arrTBoxes() As Class1" into a
normal module and change Private to Public. Most, though not all
textbox
events can be included in the withevents class.

Regards,
Peter T

"Minitman" wrote in message
.. .
Greetings,

I have several TextBoxes numbered 1 thru 200. The code in each of
these TextBoxes is the same except for a reference to the TextBox
number.

Example:

Private Sub TextBox99()
Call MySub 99
End Sub

Is there anyway to replace the figure "99" with some code that gets
that number from the sub name?

Any help would be appreciated.

-Minitman