Cell change event

[sgroves]

Howdy all!

Lets say I have: =SUM(A1:A10) in cell A11 and the given range has some random numbers, now when a user changes any of the cells within the range A1:A10 then A11 will have a new value, how do i detect the change in A11? it changes due to a formula not a physical change in that cell by the user. the Worksheet_Change does not work.

another question. A11 = SUM(A1:A10), how via VBA do i get the result of that formula ? lets say the result is 23 i want to display a message box saying "23". the value property does not seem to work. I'm using office 2010

[TMS]

Well, you can actually:

Please Login or Register  to view this content.

Regards, TMS

[sgroves]

Thanks but its does nothing. A11 changes when i change A4 for example. But the vba script doesn't notice that A11 had changed

[TMS]

Where have you put the code?

[sgroves]

Sheet1 , This is also where the calculations, ect.. are made from which i'm working on.

[jindon]

Use Calculate event instead of Change event.

[jindon]

another question. A11 = SUM(A1:A10), how via VBA do i get the result of that formula ? lets say the result is 23 i want to display a message box saying "23". the value property does not seem to work. I'm using office 2010

Try

Please Login or Register  to view this content.

[TMS]

Are macros enabled? Is event handling enabled?

In the Immediate Window, type ?Application.EnableEvents and press Enter. Is it True or False?

@jindon: you don't need to use the Calculate event; the Change event does work

[jindon]

TMS

Ah, I didn't see your code...
It will be OK in this particular case, but if the Sum range is in other sheet...

[TMS]

... but if the Sum range is in other sheet...

Ooh, I must try that It's still got precedents