Solved: Jumping to a different control on the exit of a textbox

[RoyLiam]

Hi

I have a user form that allows the user to input five client codes in consecutive text boxes, called code1, code2 etc. to code5, and then there is a button 'GetClient' that retreives information for those clients.

What I would like to do is on the exit of a text box, test to see whether a code has been entered by the user. If not, then I would like the 'active' box to jump to the GetClient control that is set in the tab order for the form to take focus after the text boxes, This would save the user have to press tab repeatedly when only inputting say one client.

I have tried the following

Private Sub code2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If code2.Text = "" Then GetClient.SetFocus
End Sub

which strangely makes the active cell jump the next control in the tab order, code3, and land on code 4, rather than jumping to the GetClient control

I have also tried

Private Sub code2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If code2.Text = "" Then GetClient.TabIndex = 2

End Sub


which doesn't seem to catch the tab order immediately; the focus goes to the next in the tab order, code3, rather than the GetClient control. If you jump back up to code1 and tab through again, it does function correctly, jumping to GetClient after code2 though it seems that you can't set something to the next in the tab order on the exit of the control before.

Any ideas or suggestions would be most welcome.

Thanks

Liam

[Bob Phillips]

Private fReEntry As Boolean

Private Sub code2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not fReEntry Then
fReEntry = True
If Me.code2.Text = "" Then
Me.code3.TabStop = False
Me.code4.TabStop = False
Me.code5.TabStop = False
Me.GetClient.SetFocus
End If
fReEntry = False
End If
End Sub

Private Sub code3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not fReEntry Then
fReEntry = True
If Me.code3.Text = "" Then
Me.code4.TabStop = False
Me.code5.TabStop = False
Me.GetClient.SetFocus
End If
fReEntry = False
End If
End Sub

Private Sub code4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not fReEntry Then
fReEntry = True
If Me.code4.Text = "" Then
Me.code5.TabStop = False
Me.GetClient.SetFocus
End If
fReEntry = False
End If
End Sub

Private Sub GetClient_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call SetTabStops
End Sub

Private Sub UserForm_Activate()
Call SetTabStops
End Sub

Private Sub SetTabStops()
Me.code1.TabStop = True
Me.code2.TabStop = True
Me.code3.TabStop = True
Me.code4.TabStop = True
Me.code5.TabStop = True
End Sub

[Tommy]

Just another way by trapping the keys Tab and Enter.:yes

I check textboxes 2-5 if null setfocus to getclient.

Dim Wht As TextBox
Private Sub TextBox2_Enter()
Set Wht = TextBox2
End Sub
Private Sub TextBox3_Enter()
Set Wht = TextBox3
End Sub
Private Sub TextBox4_Enter()
Set Wht = TextBox4
End Sub
Private Sub TextBox5_Enter()
Set Wht = TextBox5
End Sub
Private Sub TextBox3_KeyDown _
(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
KeyPress KeyCode
End Sub
Private Sub TextBox4_KeyDown _
(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
KeyPress KeyCode
End Sub
Private Sub TextBox2_KeyDown _
(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
KeyPress KeyCode
End Sub
Private Sub TextBox5_KeyDown _
(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
KeyPress KeyCode
End Sub

Private Sub KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = vbKeyReturn Or KeyAscii = vbKeyTab Then
CheckForEmpty Wht, KeyAscii
End If
End Sub
Sub GoBox()
UserForm1.GetClient.SetFocus
End Sub
Sub CheckForEmpty(iBx As TextBox, KeyAscii As MSForms.ReturnInteger)
If iBx.Text = vbNullString Or _
(UserForm1.ActiveControl.Text = vbNullString And _
iBx.Name = UserForm1.ActiveControl.Name) Then ' a double check in case the control has the text deleted after entry
GoBox
KeyAscii = 0
End If
End Sub

[RoyLiam]

Thanks for the replies - both of them work a treat!:yes