This series of blogs created by datanology are updated as and when new features, problems & solutions or general tips regarding a particular function in Excel is experienced in our projects. The aim is to provide a very short title but to cover every aspect that we can with it. Where code or formulas can be included we will attempt to explain them in plain English.
TabIndex is a function used within Userforms in Excel. We are currently using Excel 2016. It allows a developer to set the order in which the SetFocus follows, ie which textbox, or radio button is activated next following the pressing of the ‘Tab’ key.
First to note are any anomalies that we have come across and solved.
I’m changing the TabIndex but it resets itself to another value:
We found this occurs when frames are used in the Userform. If we have 5 frames for instance, Frame1, Frame2….Frame5 each frame is ‘TabIndexed’ independently of any other, even those frames inside frames. Why the value changes after you set it using the TabIndex on the properties, we can’t yet explain but I’m sure we’ll find out in time. The solution here is to set the TabIndex of the frames 0 to x in the order that you would like the user to use the frames and form. Then, inside each frame, give a value to the first field as a zero, then the order you want to see using 1,2,3…etc. Move on to the next frame and start with 0 again.
You may also note that when dealing with controls inside a frame, the maximum TabIndex value is the number of controls – 1 (as the first is always 0). If you have 5 controls and assign a TabIndex value of 10 to the bottom one, it will automatically revert to 4 as the value.
I want to auto set the order when a form opens:
This is possible under the Userform_Initialize event code. In order to work with any reliability though, your controls (textboxes, buttons etc) need to be named consistently and with a logical numerical order in relation to how you want the order to follow. We use this approach on our database systems, naming each control D1,D2…D5000 etc. This allows us to set the tabindex in the initialize as long as the controls stay in order in the form. If the client chooses to move fields at any time during the development, we have to consider setting the tabindex manually, or at least part of it manually. If your controls are named D1,D2…etc the code that you need to add to the Userform_Initialize is:
For tbindx = 1 to 99 'Assuming you have controls named from D1 to D99
Me.Controls("D" & tbindx).tabindex = tbindx
If you have had to move any fields and wish to automate part or all of it again, you may need to adapt and add a few loops and maybe a few static depending on the fields moved.
For tbindx = 1 to 24
Me.Controls("D" & tbindx).tabindex = tbindx
For tbindx = 26 to 99
Me.Controls("D" & tbindx).tabindex = tbindx-1
Note that D25 is missed out. We are assuming it’s been moved to the end and will now need to become tabindex = 99. This would be easier to set the tabindex value as 99 manually.
I use a Mac, are there any known issues ?
Unfortunately even up as far as Excel 2016 and Office 365 there are still anomalies between PC Excel and Mac Excel. Although the majority of VBA works seamlessly between the 2 you may still get issues now and then. There aren’t any problems we know about when using the Mac and TabIndex when it comes to setting them manually, although some reports have suggested that the code to set them, as exampled above may not function properly. We have had no issues, but with the introduction of Excel 2016 on the Mac, there may be problems afterall, and using anything before Excel 2011 on the Mac may be problematic too with TabIndex. If you do have any issues, contact us using the form below and we’ll see if we can establish a solution or a Mac workaround for you.
Any tips for using TabIndex ?
Using a well constructed TabIndex on userforms will make the end user experience far easier, and will give a professional look and feel to your application. There is no right way to do it, ie left to right or top to bottom before going along as it is entirely according to the design of the form and system and what the end user needs. If your system may need additional fields in the future, one tip we can offer is to number the index using steps such as 1,3,5,7,9….99 or 0,2,4,6,8…100 etc. This means that if you ever need to add in a new field in the system, it can be slotted in easily and assigned an incrementally easy TabIndex value. Likewise, if you need to add several in an area, at least it means you will only need to change a minimal number of surrounding TabIndex values, saving you time and effort on what can be a mundane task.