Combo box and Worksheet_Change

[sebt]

Hi

I've noticed some weird behaviour using a Combo box linked to a cell.
(For clarity, this is a Combo from the Control toolbox, not a
data-validation combo or a Forms combo).
(XL2000)

The Worksheet_Change event doesn't fire when a new value is selected in
the combo. Well, I expected this, there's a combo Change event isn't
there? What's odd is that when you select a new value, and then move
away from the cell, so that the cell itself definitely has a new value,
the Worksheet_Change event still doesn't fire.

I've noted posts about the data-validation combo not firing
WOrksheet_Change in XL97 - but its seems this problem applies to
Control Toolbox combos in 2000 as well. I'm not too worried as there's
a workaround (I'm going to use the Combo's events) - anyone else come
across this?

cheers


Seb

 

[Kris]

Worksheet_change is not fired in excel 2003, either.

You must use combobox_change.
in combobox_change set cell value "manually" which will fire
worksheet_change event.

 

[sebt]

Thanks for that Kris. What a pain! In the end I didn't use the
combo's Change event, as that can fire repeatedly as the user selects
various options before "making up his mind" - or, worse, every time the
user types a character, if the combo allows it.

What a pity these combos don't implement a BeforeUpdate/AfterUpdate
like the Access ones - events that fire before/after the combo is going
to update its LinkedCell. Call me a bit biased as I've been
programming Access for 10 years and Excel only intermittently, but IMHO
these Excel combos offer far less functionality.

The workaround I found was to use Worksheet_SelectionChange: in this
event, I loop through every combo on the workbook (luckily only 6 of
them) and force a call to Worksheet_Change, specifying the Target as
the combo's LinkedCell. Not ideal I know.

cheers


Seb