I am trying to tab backwards in some textboxes on a UserForm when Shift
and Tab are pressed. I need to call another sub to validate the value
before I allow the user to exit the current textbox.

It works when the user tabs forwards, I use this code for that...

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 2
End Select
End Sub

(This calls my validation sub "ntl" and sends the number of the textbox
to it, in this example 2)

To tab backwards I am trying

Private Sub TextBox2_KeyDown(ByVal KeyAscii As MSForms.ReturnInteger,
ByVal Shift As Integer)
If Shift < 1 Then Shift = 0: Exit Sub
If KeyAscii = 9 And Shift = 1 Then ntlbak 2
End Sub

I want this to call my validation sub "ntlbak" and sends the number of
the textbox to it, in this example 2)

But this code does not do it. What am I missing?

Another probably connected problem,

I have noticed a very strange behaviour when I press the ctrl button a
few times, it really messes up the textbox and empties it after
seemingly tabbing the existing value around a little. Can this be
averted?

Garry Jones
Sweden

I don't have an answer, just a suggestion.

Personally, I find it much more efficient to do the validation when the user
closes the UserForm. If the controls don't contain valid data, show a MsgBox
and keep the UserForm open. Doing validation when exiting each control
requires all kinds of messy code (as you discovered).

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss


"Garry Jones" wrote in message
...
I am trying to tab backwards in some textboxes on a UserForm when Shift
and Tab are pressed. I need to call another sub to validate the value
before I allow the user to exit the current textbox.

It works when the user tabs forwards, I use this code for that...

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 2
End Select
End Sub

(This calls my validation sub "ntl" and sends the number of the textbox
to it, in this example 2)

To tab backwards I am trying

Private Sub TextBox2_KeyDown(ByVal KeyAscii As MSForms.ReturnInteger,
ByVal Shift As Integer)
If Shift < 1 Then Shift = 0: Exit Sub
If KeyAscii = 9 And Shift = 1 Then ntlbak 2
End Sub

I want this to call my validation sub "ntlbak" and sends the number of
the textbox to it, in this example 2)

But this code does not do it. What am I missing?

Another probably connected problem,

I have noticed a very strange behaviour when I press the ctrl button a
few times, it really messes up the textbox and empties it after
seemingly tabbing the existing value around a little. Can this be
averted?

Garry Jones
Sweden

I am not sure that the following suggestion will work, but it is worth a
try. I have a sheet that I am able to tab forward or backward using te tab
button or the Shift + Tab respectively by protecting the sheet. To protect
the sheet, simply click on the "Tools" menu and then "Protect Sheet". Give
you sheet and password if you prefer. Retype the input and that should do
the trick.
I hope it helps,
Take Care and good luck.
yobeee


"Garry Jones" wrote in message
...
I am trying to tab backwards in some textboxes on a UserForm when Shift
and Tab are pressed. I need to call another sub to validate the value
before I allow the user to exit the current textbox.

It works when the user tabs forwards, I use this code for that...

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 2
End Select
End Sub

(This calls my validation sub "ntl" and sends the number of the textbox
to it, in this example 2)

To tab backwards I am trying

Private Sub TextBox2_KeyDown(ByVal KeyAscii As MSForms.ReturnInteger,
ByVal Shift As Integer)
If Shift < 1 Then Shift = 0: Exit Sub
If KeyAscii = 9 And Shift = 1 Then ntlbak 2
End Sub

I want this to call my validation sub "ntlbak" and sends the number of
the textbox to it, in this example 2)

But this code does not do it. What am I missing?

Another probably connected problem,

I have noticed a very strange behaviour when I press the ctrl button a
few times, it really messes up the textbox and empties it after
seemingly tabbing the existing value around a little. Can this be
averted?

Garry Jones
Sweden

John Walkenbach wrote:

I don't have an answer, just a suggestion.

Personally, I find it much more efficient to do the validation when the user
closes the UserForm. If the controls don't contain valid data, show a MsgBox
and keep the UserForm open. Doing validation when exiting each control
requires all kinds of messy code (as you discovered).

The user has to enter 18 values. But no two values can be alike.

I prefer to check on each input because I can trap the user in that
textbox, highlight the text and issue a msgbox. If I check on userform
close it's going to cause more problems as more than one "double value"
can exist, or even a tripple value.

I have now fixed the code, and can tab backwards and forwards. I used
the "case" solution for both instances keypress and keydown.

But now I have a remaining problem that when the user presses "ctrl and
tab" it enters a tab in the box, I test for "Null" but a blank tab is
not null and the validation I am doing sees a "blank" tab as being okay.
These values are alphanumeric, but I can only find IsNumeric in Excel
VB. How do I test for Alphanumeric? As the initial problem is now fixed
I am also going to repaste this as a new thread.

Garry Jones
Sweden