CODEWITHSUNDEEP
excelvba
gvw
gvw

Reputation: 71

VBA excel combobox.dropdown method only works with every other box

I have a form with 3 comboboxes. When run I wish to be able to cycle through them using the Tab key and when each box has focus for the list to drop down automatically (so I don't have to press the down arrow).

In the form code I have the following 

Private Sub ComboBox1_Enter()
ComboBox1.DropDown
End Sub

With the same for combobox 2 & 3

However this will only work for every other box. On initial run, combobox1 has focus - No drop down appears. Press Tab & combobox2 gets focus and dropdown appears. Press again cpmbobox3 gets focus - No dropdown.

Press again combobox1 takes focus again and dropdown list apears and so on, If I Click on any box, the list will dropdown.

If I put an object such as a text box in between each combobox then the dropdown method will work for each combobox.

Any one any ideas why the dropdown method won't work for consecutive comboboxes when using Tab?

Upvotes: 1

Views: 2969

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Yes that happens because the TABing interferes with the normal functioning. Try this (TRIED AND TESTED)

LOGIC: Capture the Tab key (keycode: 9) and set it to 0 and then move to the next combo using code.

CODE:

Option Explicit

Dim i As Long

'~~> Adding Sample Data
Private Sub UserForm_Initialize()
    For i = 1 To 10
        ComboBox1.AddItem i: ComboBox2.AddItem i: ComboBox3.AddItem i
    Next i
    ComboBox1.DropDown
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 9 Then
        KeyCode = 0
        ComboBox2.SetFocus
        ComboBox2.DropDown
    End If
End Sub

Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 9 Then
        KeyCode = 0
        ComboBox3.SetFocus
        ComboBox3.DropDown
    End If
End Sub

Private Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 9 Then
        KeyCode = 0
        ComboBox1.SetFocus
        ComboBox1.DropDown
    End If
End Sub

Upvotes: 1

Related Questions