Up/Down Arrow keys no longer work in ComboBox drop-down list with VBA code - can anyone help?

%3CLINGO-SUB%20id%3D%22lingo-sub-1654462%22%20slang%3D%22en-US%22%3EUp%2FDown%20Arrow%20keys%20no%20longer%20work%20in%20ComboBox%20drop-down%20list%20with%20VBA%20code%20-%20can%20anyone%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1654462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EAm%20hoping%20someone%20can%20shed%20some%20light%20on%20what%20is%20a%20small%2C%20but%20annoying%20problem.%26nbsp%3B%20I%20have%20a%20searchable%20combo%20box%20with%20VBA%20code%20(not%20written%20by%20myself%20but%20culled%20from%20various%20internet%20sources%2C%20mainly%20stackexchange)%20%3C%2FSPAN%3Ein%20my%20spreadsheet%20to%20enable%20the%20user%20to%20type%20part%20of%20a%20word%20then%20pick%20from%20a%20list.%26nbsp%3B%20%3CSTRONG%3EIt%20used%20to%20be%20possible%20to%20move%20through%20the%20list%20with%20the%20up%2Fdown%20arrow%20keys%2C%20but%20recently%20this%20functionality%20seems%20to%20have%20stopped%20working%20and%20pressing%20the%20down%20key%20just%20selects%20the%20first%20item%20in%20the%20list.%3C%2FSTRONG%3E%26nbsp%3B%3CSPAN%3E%20I'm%20wondering%20if%20it's%20a%20setting%20in%20Excel%20or%20my%20ComboBox%20that%20I've%20missed%2C%20a%20problem%20with%20my%20code%20or%20something%20else%20entirely...%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%20file%20with%20the%20VBA%20code%20-%20and%20copied%20the%20relevant%20section%20below.%3C%2FP%3E%3CP%3EI%20have%20searched%20this%20forum%20and%20many%20others%20in%20the%20hope%20of%20finding%20the%20answer%2C%20many%20people%20seem%20to%20have%20the%20same%20problem%2C%20but%20no-one%20seems%20to%20have%20a%20solution.%3C%2FP%3E%3CP%3EIf%20anyone%20can%20help%2C%20I'd%20be%20most%20grateful%2C%20it's%20driving%20me%20round%20the%20bend!%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%20Alison.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%23000080%22%3EPrivate%20Sub%20ComboBox_KeyDown(ByVal%20KeyCode%20As%20MSForms.ReturnInteger%2C%20ByVal%20Shift%20As%20Integer)%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%23000080%22%3E%26nbsp%3B'%20To%20enable%20arrow%20keys%20in%20a%20combo%20box%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Select%20Case%20KeyCode%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Case%2034%20'vbKeyPageDown%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20ComboBox.ListIndex%20%26lt%3B%26gt%3B%20ComboBox.ListCount%20-%201%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ComboBox.ListIndex%20%3D%20ComboBox.ListIndex%20%2B%201%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ComboBox.ListIndex%20%3D%200%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Case%2033%20'vbKeyPageUp%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20ComboBox.ListIndex%20%26lt%3B%26gt%3B%200%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ComboBox.ListIndex%20%3D%20ComboBox.ListIndex%20-%201%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ComboBox.ListIndex%20%3D%20ComboBox.ListCount%20%2B%201%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%23000080%22%3E'Code%20to%20save%20entry%20and%20move%20along%20to%20next%20cell%20when%20if%20Tab%20or%20Enter%20are%20pressed%20-%20THIS%20BIT%20WORKS%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%23000080%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%209%20'Tab%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCell.Value%20%3D%20Me.ComboBox.Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCell.Offset(0%2C%201).Activate%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%2013%20'Enter%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCell.Value%20%3D%20Me.ComboBox.Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCell.Offset(0%2C%201).Activate%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'do%20nothing%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20End%20Select%3CBR%20%2F%3EEnd%20Sub%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1654462%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

Am hoping someone can shed some light on what is a small, but annoying problem.  I have a searchable combo box with VBA code (not written by myself but culled from various internet sources, mainly stackexchange) in my spreadsheet to enable the user to type part of a word then pick from a list.  It used to be possible to move through the list with the up/down arrow keys, but recently this functionality seems to have stopped working and pressing the down key just selects the first item in the list.  I'm wondering if it's a setting in Excel or my ComboBox that I've missed, a problem with my code or something else entirely...?

 

I have attached an example file with the VBA code - and copied the relevant section below.

I have searched this forum and many others in the hope of finding the answer, many people seem to have the same problem, but no-one seems to have a solution.

If anyone can help, I'd be most grateful, it's driving me round the bend!

Thanks in advance, Alison.

 

Private Sub ComboBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 ' To enable arrow keys in a combo box
   Select Case KeyCode
      Case 34 'vbKeyPageDown
      If ComboBox.ListIndex <> ComboBox.ListCount - 1 Then
        ComboBox.ListIndex = ComboBox.ListIndex + 1
      Else
        ComboBox.ListIndex = 0
      End If
         Case 33 'vbKeyPageUp
      If ComboBox.ListIndex <> 0 Then
         ComboBox.ListIndex = ComboBox.ListIndex - 1
      Else
        ComboBox.ListIndex = ComboBox.ListCount + 1
      End If
'Code to save entry and move along to next cell when if Tab or Enter are pressed - THIS BIT WORKS
    Case 9 'Tab
      ActiveCell.Value = Me.ComboBox.Value
      ActiveCell.Offset(0, 1).Activate
    Case 13 'Enter
      ActiveCell.Value = Me.ComboBox.Value
      ActiveCell.Offset(0, 1).Activate
    Case Else
        'do nothing
   End Select
End Sub
0 Replies