Empty ComboBox after selecting item.

[daviddoria]

I am trying to populate a combobox when it is clicked (dropped down). I tried this:

Please Login or Register  to view this content.

When I click the box, it does drop down and show the two items in the list, but when I select (click) one of the items, the drop down list goes away (as it should) but the combobox is empty (it does not display "Test1" if that is the one I clicked, etc). If I remove the Clear call, it works correctly, but obviously ever time I click the box the list length gets incremented by 2 (which is bad).

In my real code I am populating this box from a range of cells specified by another combobox, so I don't have the option of just setting the InputRange or something like that, it has to be done dynamically.

I tried this in the GotFocus (instead of DropButtonClicked) as well, and it produces even weirder results. When I click the combobox, sometimes the list shows up like normal. Other times the list shows up as only one line with little tiny scroll buttons (more like a list box than combobox). Other times there are worse/strange rendering artifacts (like the box is kind of drawn but not quite). I'm guess that this again is due to the .Clear call at the beginning of the function?

Any suggestions on how to do this properly? I'm using Excel 2010.

Thanks!

David

[Tinbendr]

DropButton fires whenever it opens or closes the combobox, so it is firing twice. Once when you click it, and again when you select a item.

One workaround would be to put the code to fill the combobox in the Workbook_Open event, but his wouldn't make the list very dynamic. So the other workaround is a combination of two events.

Please Login or Register  to view this content.

[daviddoria]

That does the trick, thanks!

[Cutter]

@ daviddoria

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.