Trigger Macro by change in formula value within a cell
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:
Private Sub Worksheet_Activate()
O18Val = [O18]
Private Sub Worksheet_Calculate()
If [O18] <> O18Val Then
Application.EnableEvents = False
'run code here
Application.EnableEvents = True
O18Val = [O18]
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
I think this should work for you
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = ActiveSheet.Range("O18")
If Target.Value <> "" Then
MsgBox "Cell O18 has change!"
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.
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.
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O18")) Is Nothing Then
MsgBox "O18 has changed"
If Not Intersect(Target, Range("O19")) Is Nothing Then
MsgBox "O19 has changed"
Last edited by mbarron; 04-01-2010 at 01:50 PM.
Reason: Didn't read the post correctly
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target
Message = "XXX"
Message = "YYY"
Message = "ZZZ"
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?
Have a look at this recent thread
MVP (Excel 2008-2010)
"Provide sample data and layout if you want a quicker solution." - MD
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks alot ! Its working.