Switch textboxes in an array? textbox(array(x)).value, next x

[Flammy]

I would like to do something like what is in the title... switch between textboxes (all ordered name-wise numerically) using a loop or for. I'm currently doing the following, which isn't very scalable.

I get some error when I try to run, can't remember exactly what the message said, sorry.

If TextBox2.Value <> "" Then
numberofsheets = numberofsheets + 1
textboxname(2) = TextBox2.Value
    If TextBox3.Value <> "" Then
    numberofsheets = numberofsheets + 1
    textboxname(3) = TextBox3.Value
        If TextBox4.Value <> "" Then
        numberofsheets = numberofsheets + 1
        textboxname(4) = TextBox4.Value
            If TextBox5.Value <> "" Then
            numberofsheets = numberofsheets + 1
            textboxname(5) = TextBox5.Value
            End If
        End If
    End If
End If

I go on to then switch to the sheet (which the user has put into the textbox) and process it.

 

[AlphaFrog]

If you are using ActiveX TextBoxes on a worksheet, try something like this...

    [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] x = 2 [COLOR=darkblue]To[/COLOR] 5
        [COLOR=darkblue]With[/COLOR] Me.OLEObjects("TextBox" & x).Object
            [COLOR=darkblue]If[/COLOR] .Value <> "" [COLOR=darkblue]Then[/COLOR]
                numberofsheets = numberofsheets + 1
                textboxname(x) = .Value
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] x

If you are using TextBoxes on a UserForm, then use this With line instead...
With Me.Controls("TextBox" & x)

 

[Flammy]

If you are using ActiveX TextBoxes on a worksheet, try something like this...

    [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] x = 2 [COLOR=darkblue]To[/COLOR] 5
        [COLOR=darkblue]With[/COLOR] Me.OLEObjects("TextBox" & x).Object
            [COLOR=darkblue]If[/COLOR] .Value <> "" [COLOR=darkblue]Then[/COLOR]
                numberofsheets = numberofsheets + 1
                textboxname(x) = .Value
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] x

If you are using TextBoxes on a UserForm, then use this With line instead...
With Me.Controls("TextBox" & x)

I'm using textboxes on a userform, thanks for the help.

 

[tlowry]

Here’s my suggestion:

1. Create an array of Textbox controls:

TBoxes = Array(TextBox2, TextBox3, TextBox4, texbox5)

1. Cycle through the array, processing each control separately

ProcessTB(tbox, numberofsheets, textboxname(i))

This allows easier maintenance. In order to change the controls, or their order of processing all that needs to be done is to change the array.

Full code:

Sub tb()
    Dim TBoxes: TBoxes = Array(TextBox2, TextBox3, TextBox4, texbox5)
    ReDim textboxname(2 To UBound(TBoxes) + 2) ‘why start as 2?
    i = 2
    For Each tbox In TBoxes
        If ProcessTB(tbox, numberofsheets, textboxname(i)) = False Then Exit For
        i = i + 1
    Next tbox
End Sub
Function ProcessTB(tbox, numberofsheets, tbname) As Boolean
    ProcessTB = False
    If tbox = "" Then Exit Function
    numberofsheets = numberofsheets + 1
    tbname = tbox.Value
    ProcessTB = True
End Function