Dynamic TabStop and TabIndex in 3-frame UserForm

boxxcar

New Member
Joined
Apr 16, 2020
Messages
20
Platform
  1. Windows
I am currently stumped trying to dynamically change the TabIndex and TabStop values for multiple Textboxes on my userform frmSalesEbay.

Why doesn't this code succeed?
There are Textboxes in three Frames, Frame1, Frame3, Frame4. Can this be an/the problem?

User clicks a CommandButton. Macro successfully adjusts which Textboxes are Visible = True/False and Enabled = True/False. Macro does not successfully adjust TabStop = True/False nor TabIndex = x.

If x = 1 Or x = 3 Or x > 4 And x < 20 Or x = 26 Or x = 30 Then

With frmSalesEbay.Controls("TextBox" & x)

.Visible = True

.Enabled = True

If x = 5 Then

.TabStop = True

.TabIndex = 0 (or appropriate digit for location on form)

End If

End With

End if

How can I dynamically change the TabStop and TabIndex values? I have found code snippets online and attempted to adapt them but without success yet.
This is one. It doesnt set TextBox30 TabIndex to zero.
Sub tabset()

'tabset

Dim i As Integer

For i = 1 To 30
If i = 30 Then
frmSalesEbay.Controls("TextBox" & i).TabIndex = i - 1
End If
Next i

End Sub

I have searched for, read and tried multiple "How to programmatically set TabIndex for UserForm Controls. None have worked for me.
Your guidance will be greatly appreciated
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,846
Where is that code? In the Intialize or Activate event of the userform?
Take care about the order in which you adjust the TabIndex of different controls. It would be best if they were done in ascending order of desired Tab order.
 

boxxcar

New Member
Joined
Apr 16, 2020
Messages
20
Platform
  1. Windows
Neither. Its in the code for a CommandButton16_Click() on the UserForm frmSalesEbay.

Your question makes me suspicion I should have an Intialize or Activate event. The form has 5 CommandButtons and the code for each button visualizes the appropriate textboxes for that process. I want a different TabIndex scheme in the different processes and to set the TabStop = True for the visible textboxes.

What would the code look like in an intialize or Activate event? While I await your response I will read up on these two events. Thanks for your help.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,846
The code would look the same in any event.
Since the unneeded boxes are being made invisible, I would set the TabStop property to True in the Properties Window and design time and not change it.

Note that the TabOrder of a control is relative to the other controls in the same container (Userform,Page or Frame). So you may need to account for that in your coding.

It would be easiest if you could set the Tab order at design time, i.e. not by code. and then change the visibility and position of the needed controls to match your intended workflow.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,062
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you change the tab index on one control it can have a ripple effect, i.e. it will change the tab index of other controls.

What are you trying to achieve by changing the tab index of the controls 'dynamically'?
 

boxxcar

New Member
Joined
Apr 16, 2020
Messages
20
Platform
  1. Windows
This seems like it might meet my needs:
"
UserForm_Initialize gets called when the UserForm is instantiated.
Instead, put the commands you want into a public procedure in the UserForm code module:

....? so is this where I would put the code to visualize the desired Textboxes and to set TabStop = True
? for the first CommandButton

Public Sub ResetForm
' put stuff in here

? so is this where I would put to code to change the TabIndex and TabStop for the second CommandButton?

If you change the tab index on one control it can have a ripple effect, i.e. it will change the tab index of other controls.

What are you trying to achieve by changing the tab index of the controls 'dynamically'?

End Sub

Then if you need to reset the UserForm (let's assume it's named UserForm1), call the code this way from a regular module:

Sub Change()
UserForm1.ResetForm
End Sub
 

boxxcar

New Member
Joined
Apr 16, 2020
Messages
20
Platform
  1. Windows
If you change the tab index on one control it can have a ripple effect, i.e. it will change the tab index of other controls.

What are you trying to achieve by changing the tab index of the controls 'dynamically'?
The form will write data to 3 different worksheets depending on which CommandButton user clicks. 2/3s of the columns on each worksheet have the same column names. But the list of textboxes that need to be visible is different for each CommandButton. Therefore I want to make the appropriate Textboxes visible based on which CommandButton was clicked and set a different tab order so the movement of the cursor around the form will make sense to the user. And I want to make invisible the Textboxes not needed for the process and change the TabStop to False.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,062
Office Version
  1. 365
Platform
  1. Windows
How will setting the TabIndex property of a control change it's visibility?

If you want to show/hide controls use the Visible property.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,889
Messages
5,574,846
Members
412,620
Latest member
sharma7s
Top