VBA USERFORM: PREVENT COMBOBOX ESCAPE ON KEYDOWN

Question

Community,

There is a way to prevent an active combobox from losing its focus when hitting the down arrow (or up arrow) when at the end (or start) of a list. If there is a better way to do this (preferably with an MS standard property) please share.

Problem: When at the end of a list in a ComboBox, if you hit the down arrow it will move you to whatever control is physically below the active combobox. Vice versa for being at the top of a combobox and hitting the up arrow. This is sloppy and counterproductive. MS Excel 2013.

Solution: To prevent this lost focus, in the userform's ComboBox code you can enter the following:

Private Sub Item1_DropDown_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Select Case KeyCode
    Case vbKeyDown
        If Item1_DropDown.ListIndex = Item1_DropDown.ListCount - 1 Then
            Item1_DropDown.ListIndex = Item1_DropDown.ListIndex - 1 'when at the bottom, stay in active combobox
        Else: Item1_DropDown.ListIndex = Item1_DropDown.ListIndex 'if not at the bottom, keep moving down
        End If
    Case vbKeyUp
        If Item1_DropDown.ListIndex = 0 Then 'when at the top, stay in active combobox
            Item1_DropDown.ListIndex = 1
        Else:   Item1_DropDown.ListIndex = Item1_DropDown.ListIndex 'if not at the top, keep moving up
        End If
End Select
      ' where "Item1_DropDown" is the name of my combobox
End Sub

Okay, that's how I've been able to prevent the combobox from switching to a different control when hitting down/up when at the bottom/top of a ComboBox list.

Does anyone know of a cleaner way to do this? Maybe a way to do this without using code?

Answer 1

I don't believe there to be a non-code solution though you can shorten your code a little by simply setting KeyCode to 0 when needed

Private Sub Item1_DropDown_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Select Case KeyCode
    Case vbKeyDown
        If Item1_DropDown.ListIndex = Item1_DropDown.ListCount - 1 Then KeyCode = 0
    Case vbKeyUp
        If Item1_DropDown.ListIndex = 0 Then KeyCode = 0
End Select
      ' where "Item1_DropDown" is the name of my combobox
End Sub

you could use a class if you need this code frequently

Answer 2

There is an even sleeker way to do this. It will involve the use of a Class Module as well as the use of a Collection. These are a pair of things that will significantly improve your code (by shortening it, and sophisticating it).

Essentially, what lies beneath is an evolved use of the solutions shown above. Here it goes:

STEP #1: Create Class Module

'clsTask      (this is the 'name' of your Class Module | the name is important!)
Public WithEvents cBox as MSForms.ComboBox    'create a property for your class/object


Public Sub cBox_KeyDown(ByVal KeyCode as MSForms.ReturnInteger, ByVal shift as Integer)

    Select Case KeyCode
        Case vbKeyDown
            if cBox.ListIndex = cBox.ListCount - 1 Then KeyCode = 0
        Case vbKeyUp
            if cBox.ListIndex = 0 Then KeyCode = 0
        Case vbKeyEnd
            cBox.ListIndex = cBox.ListCount - 1
        Case vbKeyHome
            cBox.ListIndex = 0
        Case 46
            cBox.Value = ""
    End Select
'this is just the code from above
End Sub

Step #2: Declare Collection

'in a standard module            (for Excel 2013, you might need to declare this in a standard module; otherwise, anywhere else may suffice)

Public BoxColl as Collection

Step #3: Initialize Userform

 '(initialize all desired ComboBoxes in your Userform to use Trap Code (step 1 above))

'userform code

Private Sub Userform_Initialize()

    Dim ctl as Control, ox as clsTask
    Set BoxColl = New Collection
    For Each ctl in Me.Controls
        If TypeOf ctl is MSForms.Label Then            'you could also include an identifying tag here
            Set ox = New clsTask
            Set ox.cBox = ctl
            BoxColl.Add ox
        End if
    Next
'this piece of code sets all of your comboboxes as your defined object/class.
'then it puts each combobox into a collection
'all items in the collection will get the Double Click Event treatment on cue
End Sub

Okay, that's it. This is helpful if you have a bunch of ComboBoxes in your userform which you would like to apply the same code to. In this instance, if you're looking to trap the up & down buttons at the end of the list then this code will work for every single combobox.

Keep in mind, if you only want certain comboboxes to have this functionality you will need to add a tag to the ones you want. Then in the userform initialize, within the "For...Each" clause, you will need to add the conditional clause of that tag in the "if" statement.

Okay, to wrap this up. This class module is important because you will not need to maintain userform code for each & every combobox you would like. You just need to make one piece of code.

Good Luck!

Answer 3

I tried this last suggestion and it worked perfectly, after I replaced "MSForms.Label" with "MSForms.ComboBox".