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

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
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.

Code:
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.
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
If you are using ActiveX TextBoxes on a worksheet, try something like this...

Code:
    [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

Board Regular
Joined
Apr 19, 2011
Messages
51
If you are using ActiveX TextBoxes on a worksheet, try something like this...

Code:
    [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

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
Here’s my suggestion:


  1. Create an array of Textbox controls:

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


  1. Cycle through the array, processing each control separately

Code:
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:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,725
Messages
5,626,508
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top