Using Microsoft Excel 2011 on macOS Mojave, I have a UserForm with a TextBox (named NameEntry), a ListBox (named NameList), and two CommandButtons. When the TextBox has focus, I want the up and down arrow keys to act as if the ListBox has focus, that is, select the next or previous item in the ListBox, while leaving focus on the TextBox (and as a bonus, wrap at the top and bottom and move all the way up or down if shift was pressed as well). Here's the code I have:
Private Sub NameEntry_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i As Long
If KeyCode = vbKeyUp Or KeyCode = vbKeyDown Then
i = NameList.ListIndex
Select Case KeyCode
Case vbKeyUp
If Shift And 1 Then
i = 0
Else
i = i - 1
If i < 0 Then
i = NameList.ListCount - 1
End If
End If
Case vbKeyDown
If Shift And 1 Then
i = NameList.ListCount - 1
Else
i = i + 1
If i >= NameList.ListCount Then
i = 0
End If
End If
End Select
NameList.ListIndex = i
End If
End Sub
This works perfectly for up arrow. The selection moves up a line, wrapping at the top to the bottom, and shift up arrow selects the first item. The TextBox retains focus. Shift down arrow also works perfectly, selecting the last item. Down arrow sort of works. The next item is selected, and if the last item was selected, the first item is selected, but then the focus is always transferred to the ListBox. I've tried adding NameEntry.SetFocus
at the end of the NameEntry_KeyDown
sub, I've added KeyPress and KeyUp subs and watched when they're called, and am so far baffled. Up, shift up, and shift down leave the TextBox with focus, down focuses on the ListBox. How do I get down arrow to leave the focus on the TextBox?
(I might be able to have a global DoingArrowStuff
variable, and cancel exit from the TextBox if it's set, but if for some reason NameEntry_Exit
isn't called, the variable will remain set and prevent exit later. That seems like a fragile solution.)
I tweaked your code a little to make it more concise, but the main addition was the KeyCode = 0
line:
Private Sub NameEntry_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyUp
KeyCode = 0
If Shift = 1 Then
NameList.ListIndex = 0
Else
If NameList.ListIndex > 0 Then
NameList.ListIndex = NameList.ListIndex - 1
Else
NameList.ListIndex = NameList.ListCount - 1
End If
End If
Case vbKeyDown
KeyCode = 0
If Shift = 1 Then
NameList.ListIndex = NameList.ListCount - 1
Else
If NameList.ListIndex < NameList.ListCount - 1 Then
NameList.ListIndex = NameList.ListIndex + 1
Else
NameList.ListIndex = 0
End If
End If
End Select
End Sub
The affect of KeyCode = 0
is to ignore the keystroke and perform your logic instead.