Setting the Tab Order of Userform Controls Programmatically

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

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

  1. 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.

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

    Then, you run:

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

  3. Without bothering the worksheet:

Leave a Reply

Your email address will not be published. Required fields are marked *