Question:

I have a user form with 86 test boxes.

When the macro is executed, the user form is populated and the 86 boxes are filled in with values from an excel sheet. The user can select which spreadsheet to take the values from.

The user can then change the values in the user form, without affecting the values in the list.

Then, the user clicks a button and the macro is supposed to do something with each value of the testboxes if the testbox is not empty.

The testboxes are called testbox1, testbox2, testbox3 etc.

I do not want to code the same command for the 86 testboxes, so I was wondering how I could code something like the following:

For i = 1 to 86
if testbox & i . value <> “” then
code…
end if
Next i

The only answer I have read to this, is to use an array. But I cannot use an array as I want the values to be shown in the text boxes.

Solution:

Private Sub CommandButton2_Click()
For Each iobj In Me.Controls
If Len(iobj.Name) >= 7 Then
If Mid(iobj.Name, 1, 7) = “TextBox” Then
IF iobj.value <> “” then
CODE HERE…
end if
End If
End If
Next iobj
End Sub