CODEWITHSUNDEEP
excelvba
David Doria
David Doria

Reputation: 10273

Populate a combobox in DropButtonClick

I am trying to populate a combobox as it is needed, by doing the population in the DropButtonClick function. As a simple example:

Private Sub cmbAdvisor_DropButtonClick()
    cmbAdvisor.Clear

    cmbAdvisor.AddItem ("Test1")
    cmbAdvisor.AddItem ("Test2")
End Sub

This works fine, and the values are shown in the drop down list. However, when I click on one of them, the drop down list goes away, and the combobox now displays nothing/blank. I would expect it to display the item that I had selected. I'm guessing the problem is that I am doing a Clear inside of this function - but how else would I go about this?

Upvotes: 0

Views: 8621

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

I would recommend not using the DropButtonClick event. Here is an alternative (WAY 1) but if you still want to use DropButtonClick then see WAY 2

Way 1

You can populate the combobox either in the UserForm_Initialize() or a button click event. For example

Private Sub UserForm_Initialize()
    cmbAdvisor.AddItem ("Test1")
    cmbAdvisor.AddItem ("Test2")
End Sub

or

Private Sub CommandButton1_Click()
    cmbAdvisor.Clear
    cmbAdvisor.AddItem ("Test1")
    cmbAdvisor.AddItem ("Test2")
End Sub

Way 2

If you still want to populate the combobox in DropButtonClick then use this

Private Sub cmbAdvisor_DropButtonClick()
    Dim prevPos As Long
    '~~> Get the position of current selection
    prevPos = cmbAdvisor.ListIndex
    
    '~~> Switch off event so that when we select an
    '~~> item, it doesn't run this proc again
    Application.EnableEvents = False
    
    cmbAdvisor.Clear
    cmbAdvisor.AddItem ("Test1")
    cmbAdvisor.AddItem ("Test2")
    
    '~~> Set the selected value
    cmbAdvisor.ListIndex = prevPos
    
    '~~> Reset events
    Application.EnableEvents = True
End Sub

In Action

enter image description here

Upvotes: 1

wbit
wbit

Reputation: 192

For an ActiveX control embedded on a sheet:

The activeX combobox processes the click event on item selection as if it were an actual dropbuttonclick - which means we need a state object.

Dim dropbuttonclicked As Boolean

Private Sub ComboBox1_DropButtonClick()
  dropbuttonclicked = Not dropbuttonclicked
  If dropbuttonclicked = True Then
    ComboBox1.Clear
    ComboBox1.AddItem ("Test1")
    ComboBox1.AddItem ("Test2")
  End If
End Sub

For a VBA ComboBox on a VBA UserForm, you might want to use Enter instead:

Private Sub cmbAdvisor_Enter()
    cmbAdvisor.Clear
    cmbAdvisor.AddItem ("Test1")
    cmbAdvisor.AddItem ("Test2")
End Sub

Upvotes: 0

Related Questions