I want an ActiveX combobox to populate itself with cells A2:A30 on a different sheet. It should ignore blank cells.

Please Login or Register  to view this content.
First I tried this under Sub VariantList_DropButtonClick. When I click the dropdown box, it runs the code and populates fine.

At first, it would populate every time I clicked it, and so I'd get loads of duplicates. I then added "VariantList.Clear" to the beginning. However when you select something from the combo box, it runs the code again, thus clearing the list and repopulating it, but leaving nothing selected.

Is there no event for Opening a combobox, rather than clicking it? I changed the event to GotFocus, but now it's doing this weird thing where whenever I select something from the combobox, it's fine, but if I deselect the combobox, the next time I try and select something, it appears all weird like a spin button list.

I intend to run this code for multiple sheets later, replacing 'Sheets("Sheet1")' with 'Sheets(strSheets)' or something similar.