Textbox and down arrow key problem

[mps]

I have four textboxes on my userform. The tabindex is set up so you can tab from textbox 1 to textbox 2 to textbox 3 to textbox 4 to commandbutton 1 to commandbutton 2 and then back to textbox 1.
This works fine.

However, if I press the down arrow in textbox 1 it jumps to textbox 3, pressing it again it jumps to commandbutton 2. Then if I press the up arrow it jumps to commandbutton 1, up arrow again jumps to textbox 4 and up arrow again jumps to textbox 2. It is frustrating me like anything. Why does not the up / down arrows follow the tab order that works fine????

If it helps I am using Excel 2010 on Windows 7 64bit. Thanks.

Martin

 

[mps]

I would really like help on this. If no-one can solve the issue of why the up / down arrow keys do this, can anyone tell me how to stop the user advancing to the next textbox etc. using the arrow keys and only allow them to do this by pressing enter or using the tab key?? Thanks.

 

[Anthony47]

You could disable the arrows when initializing the userform, using something like

Application.OnKey "{Down}", ""

and re-enable them on exiting the form with

Application.OnKey "{Down}"

BUT I believe it is too risky.

Or you could use a variable to keep the name of the last visited control and if the next selected is not the one you expect move the focus on the next one. For example:

Dim Flow  'This on top of the module with the userform code

then

Private Sub Textbox2_Enter
If Flow<>"Textbox1" And Flow <> "" then Textbox3.SetFocus
Flow="Textbox2"
'any additional instruction
End Sub

Each of the textbox control should include a similar set of instruction, so that if when you "enter" a control you are not coming from his defined predecessor the next control will be entered.

Hope this helps.

Bye

 

[John_w]

Trap the up and down arrow keys via each control's KeyDown event like this:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyDown Or KeyCode = vbKeyUp Then KeyCode = 0
End Sub

Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyDown Or KeyCode = vbKeyUp Then KeyCode = 0
End Sub

 

[mps]

Thanks John your code worked perfectly. I had previously tried Anthony's application.onkey solution but for some reason could not get it working - I wonder if it was because I was writing 'DOWN' in the code instead of 'Down'?

I am still curious as to why moving between controls using the arrow keys happened in way which was not consistent with the tab index - must be one of those strange Excel mysteries!!