In this tutorial, I am going to share with you 4 easy methods to change tabbing order in excel user forms. These methods will make your daily workflow smoother. Also throughout this tutorial, we will learn some valuable excel VBA features and shortcuts which will be helpful in many excel related tasks.
Download Practice Workbook
You can download the practice workbook from here.
4 Easy Methods to Change Tabbing Order in Excel
We have created a simple and concise excel user form to clearly explain the steps of this tutorial. This user form has 4 text boxes namely Customer ID, First Name, Last Name, and Email. In the following section, we will see how we can navigate between these text boxes by setting our custom tab order.
1. Changing Tabbing Order from View Tab in VBA Window
One of the easiest ways to change tabbing order in excel is through the View tab. Let us see how we can do that.
Steps:
- First, in the VBA window, click on View.
- Under this, select Tab Order.
- Immediately, this will open the Tab Order window where you will see all the text boxes.
- Now, click on the Move Up or Move Down buttons to set the order of tabbing.
- Finally, click OK once you are done with setting the order.
2. Set Tabbing Order from UserForm
We can also change the tabbing order in excel directly from the user form itself. Follow the steps below to achieve this.
Steps:
- To begin with, right-click on the UserForm that you are currently working with.
- Then, select Tab Order.
- As a result, you should see the Tab Order window as previously and you can now set the order of tabbing.
Read More: How to Create Excel VBA UserForm (with Detailed Steps)
3. Manually Inserting TabIndex
If we have only a few text boxes inside our excel user form, then we can set the tabbing order manually in the Properties window. Let us see how we can do this.
Steps:
- First, click on any of the text boxes.
- Then navigate to the Properties window and click on TabIndex.
- Now, enter the TabIndex of the first text box as 0.
- Similarly, enter 1 for the next text box in the sequence, 2 for the next one, and so on.
- As a result, this will set the tab order for the text boxes.
Similar Readings
- How Different Is VBA from Other Programming Languages
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- How to Create a UserForm: an Overview
- 22 Macro Examples in Excel VBA
- How to Use Excel VBA Userform (2 Suitable Examples)
4. Using Ctrl and Left Mouse Button
If we want to set the tabbing order inside an excel user form using the mouse, then there is actually a shortcut way to achieve this. See the steps below.
Steps:
- First, press Ctrl, and while pressing it, click on the text boxes in the order that you want to tab.
- Next, go to the Properties window and type 0 in TabIndex.
- Consequently, this should set the tab order that you selected.
How to Set Tab Order in Protected Excel Sheet
Sometimes if our worksheet is protected, then it becomes difficult to navigate through the cells using the tab key. In this situation, we have to find out the cells inside the worksheet that are not protected and add them to a VBA code. Let us see how to do this.
Steps:
- To start with, right-click on the tab of the protected sheet and click on View Code.
- Next, insert the following code in the new VBA window with the name of the sheet.
- Finally, when you insert data in the selected cells and press the tab key, this will follow the cell sequence that you set in the code.
Private Sub Set_Order_Protected_Sheet(ByVal trgt As Range)
Dim tabArr As Variant
Dim n As Long
tabArr = Array("B5", "C6", "D7", "E8")
Application.ScreenUpdating = False
For n = LBound(tabArr) To UBound(tabArr)
If tabArr(n) = trgt.Address(0, 0) Then
If n = UBound(tabArr) Then
Me.Range(tabArr(LBound(tabArr))).Select
Else
Me.Range(tabArr(n + 1)).Select
End If
End If
Next n
Application.ScreenUpdating = True
End Sub
Conclusion
I hope that you were able to apply the methods I showed in this tutorial to change tabbing order in excel. As you can see, you can achieve this in a variety of ways. So you should choose the one that best suits your needs. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.