PDA

View Full Version : Action when result of formula changed



paulked
02-15-2011, 03:17 AM
Hi.

I am trying to run a macro when the result of a calculation changes.

A1 on Sheet1 contains =Sheet2!A1+1

I have tried Worksheet_Change event but it doesn't detect it.

Any ideas?

Thanks

Bob Phillips
02-15-2011, 03:36 AM
Use the Calculate event.

paulked
02-15-2011, 03:44 AM
Thanks for quick reply.

I looked at that but don't understand it. Could you give me an example please?

Many thanks

Bob Phillips
02-15-2011, 09:16 AM
Something like this



Private Sub Worksheet_Calculate()
Static PrevValue As Variant

If Me.Range("L1").Value <> PrevValue Then

MsgBox "yes"
End If
End Sub

IBihy
02-15-2011, 09:16 AM
Hello,

in the VBEditor, click the sheet on which change you want to react. Above the code window, select "Calculate" from the drop-down, add your code in the sub. It's that easy.

HTH,
Isabella

paulked
02-17-2011, 08:38 AM
Thanks xld and IBihy :)

mdmackillop
02-17-2011, 11:05 AM
Please remember to mark your threads Solved.

paulked
02-17-2011, 11:53 AM
Has the 'mark it solved' link moved? Can't find it, sorry :(

mdmackillop
02-17-2011, 12:07 PM
If you're using Chrome it doesnt show, but it's in the usual place.