Want up and down arrow in TextBox to change ListBox selection

Question

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.)

Answer 1

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.