Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable to name an object in a loop
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Problems Defining Object Variable in For Each Loop | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming |