Excel VBA how to clear items of a combo box

Question

I want to show the value of a combo box in a cell. But before it, I want to clear the items of the combo box and then fill it by items. The below code works but when I add .clear to the code, It doesn't show the selected value of combo box in cell 1,4. I don't know why.
If I don't add clear to the code, the items of the combo box will be increased every time I run the program.

Private Sub ComboBox1_change()

      With Sheet3.ComboBox1

            For Each Cell In Range("A1:A15")
           .AddItem  Cell.Value
            Next
         xx = ComboBox1.Value
         Set car = Cells(1, 4)
         Cells(1, 4).Value = xx

     End With

End Sub

SWa SWa · Accepted Answer · 2015-02-17T10:17:25

Don't use .AddItem to poulate Combo/Listboxes it is very slow for > 5 items, you can use the .List property directly. This will both be more efficient and solve your issue:

Private Sub ComboBox1_change()

      Sheet3.ComboBox1.List = Range("A1:A15").Value
      Set car = Cells(1, 4)
      Cells(1, 4).Value = xx

End Sub

Excel VBA how to clear items of a combo box

1 Answers