Reference TextBox names using a variable. 1

[JazzyLee]

I have 30 TextBox items listed on a UserForm and would like to go thru each one before updating my database to make sure that a value was entered and if so, move that value to a work field. I named these boxes EntryNo1, EntryNo2, .... EntryNo30 and get them to work if I create a statement for each as follows:

If Not (EntryNo1 = Empty) Then
intOptNo1 = EntryNo1
End If

I would like to know if I can do the following in a loop instead of listing all thirty:

For i = 1 to 30
If Not ("EntryNo" & i) = Empty Then
("intOptNo" & i) = ("EntryNo" & i)
End If
Next i

If anyone can help I would very much be thankful.

 

[Zathras]

If you can use an array instead of 30 variables, then you might be able to use something like this:

In the form's code module:
[blue]

Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim nEntryNo As Integer
  For Each ctrl In Me.Controls
    If Left(ctrl.Name, 7) = "EntryNo" Then
      If ctrl.Text <> &quot;&quot; Then
        nEntryNo = Mid(ctrl.Name, 8)
        OptionsArray(nEntryNo) = ctrl.Text
      End If
    End If
  Next
  Me.Hide
End Sub

[/color]
In a separate code module:
[blue]

Option Explicit
Public OptionsArray(30) As String

Sub demo()
Dim i As Integer
Dim sOption As String
Dim sOptions As String
  UserForm1.Show
  For i = 0 To 30
    sOption = OptionsArray(i)
    If sOption <> &quot;&quot; Then
      sOptions = sOptions & vbNewLine & &quot;Option&quot; & i & &quot; = &quot; & sOption
    End If
  Next i
  MsgBox sOptions
End Sub

[/color]

 

[JazzyLee]

Zathras,
This worked WONDERFULLY. Thanks a million for the quick response. I knew there had to be an easy way to loop thru my selections ... never thought a table would do the trick.