Set focus on UserForm Textbox on tabbing from another Textbox

3
votes

I have a Userform in Excel. It has 6 Textboxes, they are the only controls on the Userform with TabStop set to True.

I can tab and move through the textboxes. I cannot get SetFocus to work on Events fired when moving between the Textboxes. I can put a CommandButton on the userform with Userform16.Textbox1.Setfocus and it works as expected to move the focus to Textbox1.

I set up a simple test event (see below) to move the textbox focus back up to TextBox1 when Textbox2 is entered. It moves focus to Textbox3 when I tab out of TextBox1.

Private Sub TextBox2_Enter()
   Cancel = True
   UserForm16.TextBox1.SetFocus
End Sub

By putting a Stop in the above, I can see that the event is firing as expected, but it will not allow me to control the focus the next control.

I get the same results with or without the Cancel = True statement in the sub.

3
excelvbauserform
If the aim is to validate data in TextBox1 before leaving it, why dont you handle it instead in TextBox1_Exit, by setting Cancel = True ? – A.S.H
The Cancel = True won't have any effect as it is not an argument in the event. I seem to recall changing the focus does not work when done in the Enter events, so you may want to consider trying a different event instead. – jkpieterse
What I am trying to do is validate the date in the textbox as I leave for the next textbox, but if it fails the edit to return focus to the original textbox and highlight the data in that textbox. I can get focus to the original text box with Cancel=True as suggested above, but doesn't give any indication to the user that this textbox is selected. Thanks. – Stan

3 Answers

1
votes

I set up a simple test event (see below) to move the textbox focus back up to TextBox1 when Textbox2 is entered, it actually moves focus to Textbox3 when I tab out of TextBox1.

You can't set focus to another control in the _Enter() event. If you try to then the code will move focus to control which has the next TabIndex

For example

Let's say you have 5 textboxes with the following TabIndex

TextBox1 (TabIndex 0)
TextBox2 (TabIndex 1)
TextBox3 (TabIndex 3)
TextBox4 (TabIndex 4)
TextBox5 (TabIndex 2)

Now if you have this code

Private Sub TextBox2_Enter()
    TextBox3.SetFocus
End Sub

The moment you press TAB from TextBox1, the focus will move to TextBox5 (and not TextBox3) as it has the next TabIndex.

Also Cancel = True will not have any effect because it is not an argument of _Enter() like it is of say Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Edit

BTW, the only time the focus will come back to Textbox1 in your scenario is when there are only two TextBoxes in the form.

0
votes

I know this is old but this answered my question and I can't comment. However, Stan in reference to your comment on June 8, 2017 I believe you were looking for something like the code below which will highlight the text in the text box when you use it with Cancel = True. I use it in the textbox exit event. This will be the indication to the user that the text box is selected. 

With Me.TextBox1
    .Value = "Full Name"
    .SelStart = 0
    .SelLength = Len(.Text)
    .SetFocus
End With
Cancel = True
0
votes

While it is true that _Enter() event can not handle .SetFocus, the _KeyDown() event can! And thats a pretty good workaround, you just need to monitor if the TAB key was pressed.

So the code would look like something similar where TextBox1 is the one you leave and TB2 is the one you enter;

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then TextBox1.SetFocus  'where 9 is the KeyCode for the TAB button
End Sub