Trigger Macro by change in formula value within a cell

[paulmer]

I wish to trigger a macro by a change in a formula value within a particular cell. I am totally green in this area, however searching through the internet came across the following script which partly solved my problem:

Dim O18Val
Private Sub Worksheet_Activate()
O18Val = [O18]
End Sub
Private Sub Worksheet_Calculate()
If [O18] <> O18Val Then
MsgBox "XXXXXXXX"
Application.EnableEvents = False
'run code here
Application.EnableEvents = True
O18Val = [O18]
End If
End Sub

The above script gives the message "XXXXXXXX" when the formula value of cell O18 changes.

Now I wish to extend the above script for other cells as well.
E.g. If formula value of cell O19 changes, a message "YYYYYYYYY" pops up.

Any ideas please? Thanks

[bubbapost]

Hello paulmer,

I think this should work for you


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = ActiveSheet.Range("O18")
If Target.Value <> "" Then
MsgBox "Cell O18 has change!"
End If
End Sub


Andy

[paulmer]

Hi Andy,

Thanks for your response, however it does not work as I expect it cause every change in any cell triggers the macro.

The script I posted in orginal message works great. I only need to be able to have a different message when the formula value of another cell changes.

I mean that when the value of O18 changes, I get the message "XXXXXXX", when the value of O19 changes I get the message "YYYYYYYY", when the value of O20 changes I get the message "ZZZZZZ", etc.

[mbarron]

Sorry - I missed the part about it being a formula.... The code I've submitted is for the change of the formula, not the formula's result.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O18")) Is Nothing Then
MsgBox "O18 has changed"
End If
If Not Intersect(Target, Range("O19")) Is Nothing Then
MsgBox "O19 has changed"
End If
End Sub

[SamT]

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target
Case Range("O18")
Message = "XXX"
Case Range("O19")
Message = "YYY"
Case Else
Message = "ZZZ"
End Select

Msgbox(Message)

[Ted]

I have to trigger a macro whenever a cell value is updated from an online database. None of the above code works for me though, when i expected it should have. Can anyone tell me what should I change in above code?

[mdmackillop]

Have a look at this recent thread (http://www.vbaexpress.com/forum/showthread.php?t=31554)

[Ted]

Thanks alot ! Its working.