vba - How use the combobox keydown event without selecting the listed item

Question

I have in my Excel VBA project, a combobox which uses a range of cells as it's list of items. I have used a filter in it, so that whenever a value is entered, the list shrinks down to the items containing the string, and dropdown list is shown. However, the problem appears, when the dropdown is shown, the navigation keys can't be used to scroll within the items. As soon as the down key is pressed the dropdown list will be filtered again.

I guess its happening because the down key while focusing on the items, is also selecting it. Hence, the combobox_change event is called automatically.

Is there a way so that I can stop the keydown event automatically selecting an item, but only scroll through them?

Answer 1

Edited Answer:

Now having built my own sheet and worked with these ideas, ironically Application.EnableEvents only helps in certain situations because the Combobox_Change() event still fires with events disabled (or seems to be the case, at least). The basic idea that I found involved manipulating the KeyCodes and setting flags. My example below involves using a ComboBox called TempCombo and running code after the TempCombo_KeyDown() event within the VBA for the sheet (I have trimmed my stuff down for example purposes):

Option Explicit
Dim Abort as Boolean

Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 38  'Up
            If TempCombo.ListIndex <= 0 Then KeyCode = 0 'ignore "Up" press if already on the first selection, or if not on a selection
            Abort = True
            If Not KeyCode = 0 Then ' If on a selection past the first entry
                KeyCode = 0
            'Manually choose next entry, cancel key press
                TempCombo.ListIndex = TempCombo.ListIndex - 1
            End If
            Me.TempCombo.DropDown
        Case 40 'Down
            If TempCombo.ListIndex = TempCombo.ListCount - 1 Then Keycode = 0 
        ' This method was from the discussion I linked, prevents "falling off the bottom of the list"
            Abort = True
            If Not KeyCode = 0 Then ' If on a selection before the last entry
                KeyCode = 0
            'Manually choose next entry, cancel key press
                TempCombo.ListIndex = TempCombo.ListIndex + 1
            End If
            Me.TempCombo.DropDown
    End Select
    Abort = False
End Sub

Private Sub TempCombo_Change()
    If Abort Then Exit Sub ' Stop Event code if flag set
    Abort = True
    ' sets the flag until finished with commands to prevent changes made by code triggering the event multiple times
    
    ' ~~~ Insert Code you want to run for other cases here ~~~

    Abort = False
End Sub
 

I used the Abort variable as a flag in the TempCombo_Change() to prevent both multiple firings of the event code, and allow keys to not change the text result of the linked cell, preventing my dynamic range from updating. Make sure both subroutines are on the sheet where the ComboBox is!

So that is a skeleton of what I did for this, if someone finds a problem let me know, but I hope this can help someone.

---

Old Answer

I'm not sure how much this will truly help, and if I had the reputation I would just submit this as a comment because this really doesn't qualify as an answer. However I had the same question and stumbled across a thread trying to answer this. I hope that one of the codes on one of Microsoft's help pages: http://answers.microsoft.com/en-us/office/forum/office_2007-customize/disable-userform-combobox-change-event-when-arrow/598b44a1-dcda-4a2c-8e12-2b84762f98ae?db=5

It seems that looking at the KeyDown events for the up and down arrows and pairing with the ComboBox_Change events would allow you to isolate them and then prevent the combobox from updating when you press up and down to navigate the list.

I recommend looking through the OP's follow up post, it is incredibly informative and helped me adapt to my own case. Also note differences between UserForm and general Excel VBAcode, for instance Me.EnableEvents for UserForm would need to be Application.EnableEvents for general excel!

I know this is old now, but in case this helps anyone, good luck!