Change Cell from Validated List Not Firing Worksheet Change Event

[marston.gould]

I have a cell whose valid values are limited through a validation list
(using a named range).
The cell uses the excel pull down feature.
When I make the change through the pull down, no worksheet change event
is fired, but
when I make the change by typing it does.

Is there a way to get around this so that I can find the change when it
occurs from changing a value through the pull down list?

 

[Tom Ogilvy]

This would describe Excel 97. You should get a change event in later
versions.

 

[marston.gould]

Maybe it has something to do with Mac version too. I'm at home using
2002 Excel but its not firing.

Since most folks at work use 97, I'm going to try and find a
workaround.

I figure I can replace Worksheet Change with the
Worksheet_Calculate event

But do you have any idea how I would replace the following:

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim Cell As Range
Set rng = Range("a1:a10") ...... using this conceptually
For Each Cell in Target
If Union(Cell, rng).Address = rng.Address Then....

I guess Calculate event doesn't use the same variables ....
I might be able to assume that the activecell is the one I'm checking
rng against.

Not sure of specifics though

 

[Tom Ogilvy]

calculate does not indicate what triggered the calculate.

Also, selecting a value from data validation dropdown does not trigger a
calculate unless there is another cell that has a formula that references
the cell with the dropdown. So you would need a dummy formula if you don't
actually have a cell dependent on the value of the cell with the dropdown.
The activeCell would probably be a valid assumption.

I can't say about the MAC.