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
Bookmarks