Combo Box – Macro (XP)

Topic

New Reply

Hi, I have a series of combo boxes and I have put some code in the _CHANGE event. The list of items in the combobox is a dynamic range name. It seems that the macro gets fired 6 or 7 times each time I change the value and this repeated firing is messing up other things. Is there a better event to use – say _LOSTFOCUS?

Reply | Quote

Replies

Also see post 526,947.

The On LostFocus event occurs only once, so you may need this workaround, but it isn’t very user friendly, since the user has to click outside the combo box before something happens. Not using dynamic ranges is the only other workaround I know.

Reply | Quote

Thanks Hans – would dynamic range names be causing the second issue too? If I don’t go dynamic, then I suppose that I would have to go the comobox.clear then comobox.additem route – correct?

Reply | Quote

Further to the above post – the firing of the macro is one issue that I am running into. Another is that my drop down box is showing the wrong number of enteries.

I’ve put together an example spreadsheet. Use the combobox at B12 to select which list to show in the combo box at B14. The values in the second (B14) combo box get updated but the number of items shown are not updated. To repeat the bug that I am running up against, select “List 2” in the first drop down box then save and close the workbook. Reopen the workbook and select “List 4” then try to select “West” from the second combo box (I only see the first two enteries as the number of enteries shown is stuck on 2).

Reply | Quote

Try this:

Private Sub ComboBox1_Change()
Me.ComboBox2.ListFillRange = “xFillRange”
Me.ComboBox2 = “”
End Sub

Reply | Quote

Beautiful! – Thx Hans.

Reply | Quote