How To Use The Number Part Of A TextBox Name As A Variable

[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()
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

 

[Zack Barresse]

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

 

[Tim Williams]

How is TextBox99 currently being called?

Tim

 

[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

 

[Peter T]

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]

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

 

[Peter T]

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]

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

 

[Jeff Johnson]

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.)

 

[Peter T]

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.)

Not sure why you say that. For sure VB6 forms and no doubt .Net offer
additional capabilities but VBA (MSForms) textboxes expose a wide range of
events, 16 of them (4 of which though are n/a in a WithEvents class).

Regards,
Peter T

 

[Peter T]

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

 

[Jeff Johnson]

Not sure why you say that. For sure VB6 forms and no doubt .Net offer
additional capabilities but VBA (MSForms) textboxes expose a wide range of
events, 16 of them (4 of which though are n/a in a WithEvents class).

I think you misunderstood. In .NET, virtually every event procedure looks
like this:

Public Sub Control_Event(sender As Object, e As <some EventArgs-related
class>)

and you can determine which control raised the event by examining the sender
parameter. VBA and VB6- do not offer this functionality. That's what I was
talking about.

 

[Peter T]

I think you misunderstood. In .NET, virtually every event procedure looks
like this:

Public Sub Control_Event(sender As Object, e As <some EventArgs-related
class>)

and you can determine which control raised the event by examining the sender
parameter. VBA and VB6- do not offer this functionality. That's what I was
talking about.

I agree that is a powerful feature, but doesn't it merely come under what I
described as "additional capabilities", indeed a very useful one.

You said, paraphrasing slightly, "in VBA it is not possible to detect the
name of the textbox that triggered the event."

' VBA Userform
Private Sub TextBox1_Change()
MsgBox TextBox1.Name
End Sub

' VBA WithEvents Class
Public WithEvents tbx As MSForms.TextBox
Private Sub tbx_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
MsgBox tbx.Name
End Sub

' VB6 Control-array (n/a in VBA)
Private Sub Text1_Change(Index As Integer)
MsgBox Text1(Index).Name
End Sub

In all the above events the object itself can be returned, and thereby its
properties such as its name. Also in the case of Userform events, the Name
is simply the characters before the "_" in the procedure name.

I fully accept VB/A does not provide the .Net functionality you illustrate,
however I still don't follow why you imply VB/A cannot return the object
from its coded events.

Regards,
Peter T

 

[Minitman]

Hey Peter,

I am not having a problem calling the procedure with the argument
"Input_", "99" hard coded for Input_99. It is the ability to call
the name of each of the 124 TextBoxes/ComboBoxes without hard coding
the name of each TextBox/ComboBox into each TextBox/ComboBox procedure
call, but rather have some code to paste into each of the
TextBoxes/ComboBoxes so that this code detects and returns the name of
the TextBox/ComboBox as the argument to be passed to the procedure,
which should tell me which TextBox/ComboBox triggered this sub

As to where do I got the "99", I just picked one of the 124
TextBox/ComboBox controls as an example, it could be any number from 1
to 124.

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

I was going to use MID() after it got to where it was sent to. That
was going to be another problem since the code wont know how many
digits are in the number portion of the name, which why I wanted to
split the name up in the first place. Any ideas on that challenge
would also be appreciated

"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 wan the routine to do (in simple terms)"

It is part of a trio of events for each control (Change, Enter and
Exit). A Boolean marker is switched on when entering and off when
exiting the control. The purpose of this marker is to disable the
Change event from triggering if the control is changed after entering
the TextBox. When the Enter event is triggered then the code in the
Change event must be disabled to avoid duplication of formatting
because the Exit event will do the formatting changes after leaving
the TextBox.

If the contents of the TextBox are changed from code rather then
entering the TextBox, then the Boolean switch will not be switched on
but will remain off. This will allow the formatting of the control to
proceed in the Change event. All of the actual formatting is done
with this called sub - less code that way.

I want to thank you for all of the time you have given me on this
question.

After reading the replies from Jeff Johnson, I'm not sure if detecting
the name of the control automatically can be done. If that's the
case, it's back to the drawing board to find a different approach to
the problem.

If it is possible, I would really appreciate knowing how to do it.

-Minitman

 

[Peter T]

First the easy one, how to split "Input_99"

I was going to use MID() after it got to where it was sent to. That
was going to be another problem since the code wont know how many
digits are in the number portion of the name,

You don't need to know how many digits

s1 = "Input_"
s2 = Mid$("Input_99", 7, 5)
num = Val(s2)

This assumes that the length of the left portion is always 6. The "5" is an
arbitrary number that will be at least the number of digits, ie length of
digits. As it seems unlikely you will have more than 999 textboxes you could
change the "5" to "3". If you might have a name like say "Input_12moretext"
use the Val() function.

It is part of a trio of events for each control (Change, Enter and
Exit).

The Withevents example I posted shows the Change event working for all your
textboxes

' in the withevents class
Private Sub tbx_Change()
tbx.Parent.Caption = "TB-" & gID & ": " & tbx.Text
End Sub

The same demo shows how to process any textbox only by supplying its number

tbID = 99
arrTBoxes(tbID).TextBoxStuff nClr

Unfortunately though the Enter & Exit events, which you say you require, do
not work in a Withevents class. Indeed this is a limitation. Sometimes there
are ways of working round this, eg by detecting other events, can be done
but quite a lot of work. However with modern machines it might not be such a
big deal to write the 124 pairs of Enter/Exit events in the form (use Excel
to help write the repetitive code in cells). For other events I'd strongly
suggest the WithEvents class.

' userform
Private Sub TextBox1_Enter() ' similar
TBX_Enter TextBox1
End Sub

Private Sub TBX_Enter(tbx As MSForms.TextBox)
Dim pos As Long
If Not mbExitEvent Then
' assumes all texbox names include a "_"
' but if they are all "Input_" change pos+1 to 7
pos = InStr(tbx.Name, "_")
MsgBox "text box : " & Mid$(tbx.Name, pos + 1, 4)
End If
End Sub

If you want to disable the event, eg you are changing focus with code,
temporarily set the module level flag mbExitEvent, but don't forget to
reset to false when done (important, double check code to ensure no
accidental way for it not to get reset, error handling etc).

Dim mbExitEvent As Boolean ' top of form code

Private Sub CommandButton1_Click()
mbExitEvent = True
TextBox1.SetFocus
mbExitEvent = False
End Sub

Get back if it's still not coming together. I still don't quite follow all
of what you are doing but whatever it is I suspect it's doable!

Regards,
Peter T

 

[Minitman]

Hey Peter,

Thanks for the info on splitting the name. Looks good, but it needs
one additional element, which is discussed next.

As for the other problem, please excuse my ignorance (I am working on
correcting this condition), there are a couple of questions on your
example that I don't understand:

Private Sub TextBox1_Enter() ' similar
TBX_Enter TextBox1
End Sub

I don't know if it makes any difference, but some of these "TextBoxes"
are really "ComboBoxes".

In your example from the previous post, you have "TextBox1" hard coded
as the argument for TBX_Enter. I was trying to get away from hard
coding these arguments by coming up with some code to be the argument.
This requested code would need to detect what the name of the sub is
(in this case TextBox1) in order to pas this detected name to the
TBX_Enter procedure. This is what I have been trying to say all
along.

I'm afraid I may have made it more complicated then it needed to be.

-Minitman

 

[Jeff Johnson]

I fully accept VB/A does not provide the .Net functionality you
illustrate,
however I still don't follow why you imply VB/A cannot return the object
from its coded events.

Okay, then how about "They don't do it unless you add extra code to allow
such a thing to be detected"? And what about controls added at run-time...?

Ultimately, most people are looking for some built-in function or property
to tell them stuff like "What's the name of the procedure I'm currently in"
or "What control called this (generic) procedure." No form of VBA provides
this inherently. That's what I was saying: there's no way to ASK the system
these questions; you have to go through what I consider a "roundabout"
method to get this info.

 

[Peter T]

Okay, then how about "They don't do it unless you add extra code to allow
such a thing to be detected"?

If you mean need to include the event code yeah I can go with that

And what about controls added at run-time...?

The normal way would be with new instances of pre made WithEvents class's.

FWIW one of the few VBA can-do but VB6/.Net can't type things is to copy
(literally copy) an entire MultiPage page plus all its controls to a new
page. Events for all the newly pasted cotrols would be handled with
WithEvents.

Thinking about it, one more VBA "can do" thing is the ability to write new
code and create new modules or import modules, at run time. But I digress.

Regards,
Peter T