which event for combobox?

[RobBroekhuis]

Hi all,
I have a combobox control on a worksheet, which I am using to set the criteria range of an advanced filter operation (Dale would be proud of me ;-)). Since the combobox choices depend on the sheet's contents, which may change, I wrote code to regenerate the combobox list dynamically, and put it in the sheet's combobox_enter event handler. It worked great - once. After first entering the combobox, it seems like the enter event is never triggered again. According to VBA help, the enter event occurs when the focus passes from one control to another on a userform. Of course, my control is not on a userform, and typically a sheet range, rather than a different control, has focus when I go to select from the combobox. I tried another event, DropButtonClick, which got me into trouble because it is triggered not only when the list drops down, but also when it comes back up. What is the appropriate event (or workaround) to use?
Rob

 

[rmikesmith]

Rob,

What about re-populating the combobox from the worksheet's Change event. You could do some checks for relevant range and whether any actual change to the list occurred before re-doing the combo. This might have the added benefit of little or no apparent wait time for the end-user; i.e. if re-population of the combo occurs when the user clicks on it, there could be some lag (depending on number of items).

BTW, where do you find a combobox_change event? I can't locate this.


Regards,
Mike

 

[RobBroekhuis]

Mike,
Thanks for the pointer. My combobox_change event works beautifully (it properly executes the advanced filter operation). You may have to have VBA extensibility library referenced in your project to get VBE to recognize it (not sure about that).
I thought about using the worksheet_change event handler, but concluded that it would be a bear to write. Adding new items to the list as the user (me, actually) puts them on the worksheet is easy enough, put deleting them when they get deleted from the worksheet is much trickier. It could be done, I'm sure, but I'd much rather do this through proper handling of the control events. It takes hardly any time to regenerate the dropdown list.
Rob