DropButtonClick

[tiredofit]

I have a combobox on my userform.

I want the code to trigger when I click on the dropdown arrow, so I used this:

Private Sub Fruit_DropButtonClick()

    With Me.Fruit
        
        .Clear
        
        .AddItem "Apples"
        .AddItem "Oranges"
    
    End With
    
End Sub

but it seems after I have clicked on the dropdown and chosen a value, the combobox is empty.

Why is that?

I added:

.Clear

because if I didn't, if I click on the dropdown n times, there will be n "Apples" and "Oranges", opposed to only having one of each.

Thanks

 

[Sahak]

Hi Tiredofit,
Maybe insteade of DropButtonClick to use MouseDown?

Private Sub Fruit_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
      With Me.Fruit
        .Clear
        .AddItem "Apples"
        .AddItem "Oranges"
    End With
    Call Fruit_Click
End Sub

Private Sub Fruit_Click()
    Range("A1").Value = Me.Fruit.Value
End Sub

 

[tiredofit]

Hi Tiredofit,
Maybe insteade of DropButtonClick to use MouseDown?

Private Sub Fruit_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
      With Me.Fruit
        .Clear
        .AddItem "Apples"
        .AddItem "Oranges"
    End With
    Call Fruit_Click
End Sub

Private Sub Fruit_Click()
    Range("A1").Value = Me.Fruit.Value
End Sub

Thanks.

What is the difference between the two?

 

[Sahak]

The difference is that
but it seems after I have clicked on the dropdown and chosen a value, the combobox is empty
after choosing a value, the combobox shows the chosen value, it is not empty. Am I mistaken?

 

[tiredofit]

The difference is that
but it seems after I have clicked on the dropdown and chosen a value, the combobox is empty
after choosing a value, the combobox shows the chosen value, it is not empty. Am I mistaken?

I mean Dropdown doesn't seem to retain the value but why?

 

[Sahak]

Sorry, misunderstood, I have no idea as well

 

[Jaafar Tribak]

I think it is because the DropButtonClick event also fires when an item from the list is chosen.

 

[tiredofit]

I think it is because the DropButtonClick event also fires when an item from the list is chosen.

Thanks.

It does seem a bit buggy though.

https://stackoverflow.com/questions/12065420/populate-a-combobox-in-dropbuttonclick

 

[Jaafar Tribak]

Checking against the ListIndex may work for you as follows :

Private Sub Fruit_DropButtonClick()
 
    If Fruit.ListIndex = -1 Then
        With Me.Fruit
            .Clear
            .AddItem "Apples"
            .AddItem "Oranges"
        End With
    End If

End Sub

 

[tiredofit]

Checking against the ListIndex may work for you as follows :

Private Sub Fruit_DropButtonClick()
 
    If Fruit.ListIndex = -1 Then
        With Me.Fruit
            .Clear
            .AddItem "Apples"
            .AddItem "Oranges"
        End With
    End If

End Sub

Thanks