RAND() Won't Trigger Worksheet_Change

[egun]

I put a random number cell on my worksheet, and I want to be able to run some
code in the Worksheet_Change routine every time that cell is updated. I have
discovered that when that cell updates, Excel is not considering it a
"change" - the event is not triggered.

Any ideas as to why this is, and how I can work around it? I know the code
is working, because if I type "=RAND()" in that cell again and hit Enter, the
event is triggered because I changed the formula of the cell. Also, if I
just type a number of text into the cell, the event is triggered.

Thanks,

Eric

 

[Dave Peterson]

Worksheet_change catches your typing.

Maybe you want to use to use the worksheet_calculate event???

 

[Gary''s Student]

You need to use a different event, the Worksheet Calculate event.

 

[egun]

Hadn't thought about Worksheet_Calculate. I'll give it a try.

Thanks guys!

 

[egun]

Okay, just realized this - the Worksheet_Calculate routine does not have
"Target" as an argument. How do I know if my specific cell has been updated?
Since it's a volatile function, is it safe to assume it changed any time the
worksheet is recalculated?

Thanks again,

Eric

 

[Dave Peterson]

For the cell with =rand(), yep. Since it's volatile.

If you had a cell that contained a formula that may not change during a
recalculation, you'd have to keep track of that cell's value your own self.