Loop through and change TextBox in UserForm

[boileauj]

I am working with a UserForm that has three sets of twenty TextBoxs that represent three different inputs that I will need for my spreadsheet.

I would like to create a For...Next Loop that will look at each one and check a value in the spreadsheet, if the number of the TextBox is not greater than the number in the cell then the TextBox value is changed to "N/A"

Code should be:

Private Sub CheckInputs()

For i = 1 to 20

If ActiveSheet.Range("C40").Value < 1 Then InputTable.txt1.Value = "N/A"

Next i

End Sub

**How do I replace the "1" in [Value < 1] and [txt1] with "i"?**



Thanks,

Jacob

 

[Norie]

Jacob

Why do you want to change the first 1?

If you do then perhaps you just need to use i.

If you want to refer to textboxes called txt1, txt2, txt3...txt20 try this.

InputTable.Controls("txt" & I).Value = "N/A"

 

[mikerickson]

This assumes that your userform is named InputTable. It also assumes that you want to make a numeric comparison between the cell's value and the textbox's entry. (The number 10 > 2, but the string "10" < "2")

Dim i as long

For i = 1 to 20
    With Me.Controls("txt" & i)
        If Val(CStr(ActiveSheet.Range("A40").Value)) < Val(.Text) Then .Text = "N/A"
    End With
Next I

 

[boileauj]

Jacob

Why do you want to change the first 1?

If you do then perhaps you just need to use i.

If you want to refer to textboxes called txt1, txt2, txt3...txt20 try this.

InputTable.Controls("txt" & I).Value = "N/A"

Thanks Norie,

That code worked perfect, it just has been bothering me for awhile how to reference the TextBox correctly with the "i" from the For...Next Loop.

I want to change the first 1 because it corresponds to how many inputs are need. For example if the value in the cell on the spreadsheet were 10, then I would want every value up to ten to have a value input in the TextBox, if not then it would be marked as N/A so that no input is required.

Thanks