use a variable to name an object in a loop

[m davidson]

Hello All,

Any help will be greatly apprecitated.

I want to loop through 49 text boxes and validate the data entered into
them. The text box names are in sequence as such: TextBox1, TextBox2,
TextBox3...etc.) Here's the code (that works) for the first text box
(TextBox1). But I don't want to enter (or update it) 49 Times. So I
was hoping to do the same thing over and over in a loop.

<<Snip>>
'check to see if it's a number and if it's positive
If IsNumeric(TextBox1.Value) = False Then
Call MsgBox("An entry in a non per diem cost field" _
& vbCrLf & " is not a valid number. Please correct" _
& vbCrLf & "the entry." _
, vbExclamation, "Not a valid number")
TextBox1.SetFocus
Exit Sub
ElseIf TextBox1.Value < 0 Then
Call MsgBox("An entry in a non per diem" _
& vbCrLf & "cost field is a negative number." _
& vbCrLf & "Please correct this entry." _
, vbExclamation, "No negative numbers")

TextBox1.SetFocus
Exit Sub
Else
'Everything is OK... do nothing
End If
<<End Snip>>

What I've been trying to do (and have been very unsuccessful) is to
create a loop and use a variable to cycle through the text boxes and
perform the same validations as above.

Here's the NON WORKING code:

<<SNIP>>
Dim varEachCell As String
Dim varChangeFocus As String
Dim varFinalChangeFocus As Object
Dim i As Integer

For i = 1 To 49
varChangeFocus = "Textbox" & i
Set varFinalChangeFocus = varChangeFocus
varEachCell = "Textbox" & i & ".value"

If IsNumeric(varEachCell) = False And IsNull(varEachCell) = False Then
Call MsgBox("You did not enter a valid number in one of the non per
diem itemized cost cells..." _
& vbCrLf & "Please check the entry and enter a valid
number" _
& vbCrLf & "The questionable entry shows: " & varEachCell
& """" _
, vbExclamation, "Not a valid number")

varFinalChangeFocus.SetFocus

Exit Sub


End If
Next i
<<END SNIP>>

Needless to say, the code fails. (miserably). What I THOUGHT I had to
do was to concatenate each text box name in a STRING variable and then
assign the string variable to an OBJECT variable. But it's blatantly
obvious that I don't have a clue as to what's going on, so I am here
humbly asking for help. The books I have demonstrate using variables
for counters, settings and such, but they havent shown me how to use
them in OBJECT names. (Or they might have and I didn't grasp it.)
Such as: MyVariable.setfocus or MyVariable.value or
"txtbox1"&Myvariable&".value" I want to be able to refer to an object
using a variable... I don't know if I am asking the question correctly.

I am trying to teach myself VBA, any help will be greatly appreciated.

Thanks,
Mike Davidson

 

[RB Smissaert]

One way of doing this is looping through the collection
of controls on the form:

Sub test()

Dim ctl As Control

For Each ctl In UserForm1.Controls
If Left$(ctl.Name, 7) = "TextBox" Then
MsgBox ctl.Name
End If
Next

End Sub


RBS

 

[Bob Phillips]

For i = 1 To 49
'check to see if it's a number and if it's positive
If Not IsNumeric(Me.Controls("TextBox" & i).Text) Then
Call MsgBox("An entry in a non per diem cost field" _
& vbCrLf & " is not a valid number. Please correct"
_
& vbCrLf & "the entry." _
, vbExclamation, "Not a valid number")
Me.Controls("TextBox" & i).SetFocus
Exit Sub
ElseIf Me.Controls("TextBox" & i).Text < 0 Then
Call MsgBox("An entry in a non per diem" _
& vbCrLf & "cost field is a negative number." _
& vbCrLf & "Please correct this entry." _
, vbExclamation, "No negative numbers")
Me.Controls("TextBox" & i).SetFocus
Exit Sub
Else
'Everything is OK... do nothing
End If
Next i


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Tom Ogilvy]

Dim tbox as MsForms.Textbox

for i = 1 to 49
set tbox = Me.OleObjects("Textbox" & i).Object



Next i


if on a userform

set tbox = Me.Controls("Textbox" & i)

Use Tbox in the remainder of your code.

 

[m davidson]

Bob,

Brilliant! Worked beautifully.

Ok, Now I want to make sure I understand. It looks as though You have
"fully declared" the name of the control. With "Me" being the current
form(?) and "Controls" being the controls class(?) on the current form.
Then we declare the concatenated name of the control in question. And
then I can add the period and call on the previously declared control's
properties or methods. (???) Is that right?

Thank you so much! That saved a whole lot of typing. And if I ever
need to change it... I won't have to change it in 49 different places.
Beautiful!

Mike

 

[m davidson]

Tom,

Thank you for the reply.
This looks very interesting. I want to make sure I understand. This
is how I can assign a control to a variable. Is that correct?

You first dimensioned the variable tbox AS "a" textbox

then I SET my newly created variable, within the loop, using the name
with the concatenated "i" counter variable.

I am guessing that the ".object" declares "tbox" as an "Object" (???
confused on that part)

now I can call on "tbox" as the currently "active" textbox within the
loop.

Can I simply use a statement such as: tbox.setfocus or
IsNumeric(tbox.value) or tbox.value =100 ?? (This possibility is
exciting)

Thanks again,
Mike Davidson

 

[Tom Ogilvy]

You never stated where the textboxes are located - but it seemed less likely
to have 49 textboxes on a userform. Anyway my first suggestion was for a
worksheet which doesn't appear to be the case.

 

[Bob Phillips]

Bob,

Brilliant! Worked beautifully.

Ok, Now I want to make sure I understand. It looks as though You have
"fully declared" the name of the control.

Well to be precise I have referenced the control through the coolection.

With "Me" being the current form(?)

The host form, which is what I think you mean by current.

and "Controls" being the controls class(?) on the current form.

No, the controls collection on that form.

Then we declare the concatenated name of the control in question. And
then I can add the period and call on the previously declared control's
properties or methods. (???) Is that right?

Well, again being precise, we didn't declare the control, but accessed it
through the controls collection within our loop. As you say, you then can
access the properties and methods in the same way, as wwe do in the line

Me.Controls("TextBox" & i).SetFocus

You could create a variable to point at the conrol

Dim ctl As Control

For i = 1 To 49
Set ctl = Me.Controls("TextBox" & i)
'check to see if it's a number and if it's positive
If Not IsNumeric(ctl.Text) Then
'etc.

and then the code would be more akin to the way you describe it.

Thank you so much! That saved a whole lot of typing. And if I ever
need to change it... I won't have to change it in 49 different places.
Beautiful!

My pleasure.