Setting the Tab Order of Userform Controls Programmatically

Posted on August 6, 2015 by Dick Kusleika

I hate designing userforms. I mean the part where I’m lining up controls, renaming controls, and all the other super-fiddly parts of making a form look and act right – like setting the tab order. I always wait until the very end so I don’t have to do it twice. And that Tab Order dialog? Forget about it.

When I’m finally ready to set the tab order, I find the first control and click Move Up until it’s at the top. Next, I find the second control and click Move Up until it’s just below the first control. Then I go to Whammyburger and force them to serve me breakfast. Then I go back and do the rest of the controls. It’s maddening. I decided finally to do something about it.

First I set a reference to the VBA Extensibility Library.

Then I put this code in a standard module

Public Sub FixTabOrder()
    
    Dim ctl As Control
    Dim i As Long, j As Long
    Dim lCnt As Long
    Dim vbc As VBIDE.VBComponent
    
    'Change this to the name of your userform
    Set vbc = ThisWorkbook.VBProject.VBComponents("UInvoice")
    
    For i = 1 To vbc.Properties("Width")
        For j = 1 To vbc.Properties("Height")
            For Each ctl In vbc.Designer.Controls
                If ctl.Top = i And ctl.Left = j Then
                    ctl.TabIndex = lCnt
                    lCnt = lCnt + 1
                End If
            Next ctl
        Next j
    Next i
    
    For Each ctl In vbc.Designer.Controls
        Debug.Print ctl.Name, ctl.TabIndex
    Next ctl
    
End Sub

Public Sub FixTabOrder()

Dim ctl As Control

Dim i As Long, j As Long

Dim lCnt As Long

Dim vbc As VBIDE.VBComponent

'Change this to the name of your userform

Set vbc = ThisWorkbook.VBProject.VBComponents("UInvoice")

For i = 1 To vbc.Properties("Width")

For j = 1 To vbc.Properties("Height")

For Each ctl In vbc.Designer.Controls

If ctl.Top = i And ctl.Left = j Then

ctl.TabIndex = lCnt

lCnt = lCnt + 1

End If

Next ctl

Next j

Next i

For Each ctl In vbc.Designer.Controls

Debug.Print ctl.Name, ctl.TabIndex

Next ctl

End Sub

This is pretty brute force, but it only take a few seconds to run, so who cares. The i and j variables move through the userform point-by-point, left to right, top to bottom (that’s the typographical point, not the agenda items point). For every point on the userform, it loops through every control to see if that control starts at that point. If it finds one, it sets the controls TabIndex to the next number. At the end it just prints out the control names and tabs.

Even if that’s not exactly how I want my tab order, it gets it close and I can make other modifications manually.

If you have a super-wide or super-long form, it will take longer. The right way to do this is to put all the controls in an array and sort them by their Top and Left properties. If this method is too slow, you should write that up. Then send me a copy.

7 thoughts on “Setting the Tab Order of Userform Controls Programmatically”

Felipe Costa Gualberto says:

August 6, 2015 at 2:31 pm

There is an easy way to set TabIndex properties.
1 – Left click in the first control;
2 – Hold Ctrl key and click in each control in the order you want;
3 – Open the Properties window
4 – Set the TabIndex property to 0 and press Enter

You’ll see that the the order will be rearranged according to the order you made your clicks.
Doug Glancy says:

August 6, 2015 at 2:48 pm

@Felipe, that’s crazy! (in a good way). I never knew that.

On a somewhat related note, I’ve got some code that lists all the accelerator keys on a form. I think it could benefit from Dick’s VBIDE code above. It’s at http://yoursumbuddy.com/listing-userform-accelerator-keys/
Dick Kusleika says:

August 6, 2015 at 4:24 pm

Genius, Felipe. Thanks.

I have that too Doug (http://dailydoseofexcel.com/archives/2008/07/22/list-userform-hotkeys/) but only to see what’s available.
Charlie says:

August 7, 2015 at 12:44 pm

@Felipe – Brilliant!!!!
Jon Peltier says:

August 8, 2015 at 7:17 pm

Felipe –

This is awesome. Thanks.

There is a handy UserForm method called SetDefaultTabOrder.
If you UserForm has a lot of controls, you can first run:

Sub ListSortedControlsTabIndex()
    Dim ws As Worksheet
    Dim frm As UserForm1
    Dim iControl As MSForms.Control
    Dim iRow As Long
    
    Worksheets.Add
    Set frm = New UserForm1
    frm.SetDefaultTabOrder
    For Each iControl In frm.Controls
        iRow = iRow + 1
        Cells(iRow, "A") = iControl.Name
        Cells(iRow, "B") = iControl.TabIndex
    Next iControl
    Unload frm
    Cells.Sort Key1:=Range("B1")
End Sub

Sub ListSortedControlsTabIndex()

Dim ws As Worksheet

Dim frm As UserForm1

Dim iControl As MSForms.Control

Dim iRow As Long

Worksheets.Add

Set frm = New UserForm1

frm.SetDefaultTabOrder

For Each iControl In frm.Controls

iRow = iRow + 1

Cells(iRow, "A") = iControl.Name

Cells(iRow, "B") = iControl.TabIndex

Next iControl

Unload frm

Cells.Sort Key1:=Range("B1")

End Sub

Then, you run:

Sub UpdateControlsTabIndex()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim lUserFormComponent As VBComponent
    
    Set lUserFormComponent = ActiveWorkbook.VBProject.VBComponents("UserForm1")
    For iRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        lUserFormComponent.Designer.Controls(Cells(iRow, "A").Value2).TabIndex = Cells(iRow, "B").Value2
    Next iRow
End Sub

Sub UpdateControlsTabIndex()

Dim iRow As Long

Dim ws As Worksheet

Dim lUserFormComponent As VBComponent

Set lUserFormComponent = ActiveWorkbook.VBProject.VBComponents("UserForm1")

For iRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row

lUserFormComponent.Designer.Controls(Cells(iRow, "A").Value2).TabIndex = Cells(iRow, "B").Value2

Next iRow

End Sub

*Change UserForm1 to the name of your target UserForm
*You must enable trust to your VBE project to run the second procedure.

Without bothering the worksheet:

Sub M_snb()
   For Each ct In UserForm1.Controls
      Application.Names.Add ct.Name, "="
   Next
   
   y = 0
   With ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
        For Each it In Application.Names
           .Controls(it.Name).TabIndex = y
           y = y + 1
        Next
    End With
End Sub

Sub M_snb()

For Each ct In UserForm1.Controls

Application.Names.Add ct.Name, "="

y = 0

With ThisWorkbook.VBProject.VBComponents("UserForm1").Designer

For Each it In Application.Names

.Controls(it.Name).TabIndex = y

y = y + 1

End With

End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Setting the Tab Order of Userform Controls Programmatically

7 thoughts on “Setting the Tab Order of Userform Controls Programmatically”

Leave a Reply