TabIndex Override

[mikeymay]

I have a Userform with several textboxes.

When text is added to a textbox and the user presses the TAB key, I want the focus to go to the next textbox on the form.

Prior to this happening though I need to validate the textbox entry -

E.g.
If a date value is required and the entry is a valid ate format, the TAB press will set the focus on the next textbox.

If the entry is not a valid date format, the textbox is cleared and the focus needs to stay with that textbox.

Currently, I cannot work out how to make the above work using the various options for the control and using the .SetFocus command is ignored when a controls .TabStop = TRUE

'When this control has its TabStop set to False, when TAB is pressed when in the textbox prior to this one, the focus doesn't set to this control

Private Sub txtPallets_AfterUpdate()

If IsNumeric(txtPallets) Then
   txtPallets = Round(intPallets, 0)
   
   intPallets = txtPallets
   Else
   MsgBox ("Enter a numeric value only"), vbExclamation, "ERROR"
   
   txtPallets = ""
    
   txtPallets.SetFocus
End If

CheckAdd

End Sub

TIA

 

[Dan_W]

Try using the BeforeUpdate event instead. When you add Cancel = True, it prevents the user from progressing to the next control in the TabIndex

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(TextBox1.text) = False Then
        MsgBox "Numbers only"
        Me.BackColor = vbRed
        Cancel = True
    Else
        Me.BackColor = vbWhite
    End If
End Sub

 

[mikeymay]

Thanks Dan_W!

This has bothered me for probably 15 years!!!