CODEWITHSUNDEEP
excelvbadrop-down-menucombobox
Casey Hungler
Casey Hungler

Reputation: 113

ComboBox dropdown only firing every other box

I have a series of combo boxes in a user form in MS Excel 2010. I have _enter events assigned to each of them which call .DropDown so that as I tab through the boxes, they will automatically open the drop down window. This works great for the first one, but when I tab to the next, it does not work. The _enter event fires (tested with msgbox), but it doesn't drop down. The following box will drop down, but not the one after that. If you tab backwards, the same thing happens in reverse. If I put dummy text boxes between them in the tab order, they all drop down.

Any ideas how to fix this?

I also tried using KeyUp -> Ascii 9 (tab) with the same results.

Upvotes: 1

Views: 80

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Before I give the solution, I will try to explain why this unexpected behavior occurs. It seems due to some event race condition.

It occurs when some combo (say ComboBox1) is opened (dropdown window opened) and then you navigate to another combo (say ComboBox2) using the TAB key. The race condition occurs because internally, the window of ComboBox1 will also handle the pressed key, so it causes the window of ComboBox2 to close immediately, because only one combo can be opened at a time on the same UserForm.

The workaround for this is to handle the KeyUp event, which is the last event that occurs once your new combo gets the focus by TAB navigation.

Solution

For each combo, add the following event handler:

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab Then ComboBox1.DropDown
End Sub

Private Sub ComboBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab Then ComboBox2.DropDown
End Sub

' similar handlers for ComboBox3, ComboBox4 etc..

These handlers are enough for the behavior you want from TAB navigation. You can have also keep the _Enter handlers as is, if you want the same behavior when selecting the combo with the mouse.

Upvotes: 1

Related Questions