Is there a better way to call macro on cell value change?

Hi,

I currently have a macro that checks one cell ("ChangeTrigger") for a change in formula value on a non-active sheet and if there is a change it then fires another macro ("Macro99"). Problem is that while this macro works, it only seems to work for a change in ONE celI and I need it...or better code...to be able to check for a value change in 3 cells and run one of three different macros depending on which cell had a change in value.

My current code is on the worksheet object:

Private Sub Worksheet_Calculate()

If range("ChangeTrigger").Value <> ChangeTrigger_Old Then

    'Prevent looping of code due to worksheet changes
    Application.EnableEvents = False
        
    Macro99
        
End If
    ChangeTrigger_Old = range("ChangeTrigger").Value
    Application.EnableEvents = True
    
End Sub

And this little pice of code is atop a module:

Public Databases_ChangeTrigger_Old As Double

Thanks,

Lawrence

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1, H3, H5"     '<== change to suit
Dim NewValue As Variant
    
    On Error GoTo ws_exit
    Application.EnableEvents = False

    NewValue = Target.Value2
    Application.Undo

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
        
            If Target.Value2 <> NewValue Then
            
                Target.Value2 = NewValue
                Call Macro99
            Else
            
                Target.Value2 = NewValue
            End If
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Bob,

That code looks very promising. Thank you!

Two questions: Will the Change event work for a formula result on a non-active worksheet? And how do I get a change in H3 and H5 to fire different macros? for example, if H3 changes, fire Macro99B; and if H5 changes, fire macro99C.

Thanks again,

Lawrence

Originally Posted by skysurfer

Will the Change event work for a formula result on a non-active worksheet?

I am not sure what you mean by this, can you elaborate?

Originally Posted by skysurfer

And how do I get a change in H3 and H5 to fire different macros? for example, if H3 changes, fire Macro99B; and if H5 changes, fire macro99C.

You have to test each cell individually for an intersection and call the appropriate macro.

Originally Posted by teylyn

skysurfer,

a Worksheet_Change event macro only monitors its own worksheet, not other sheets. You need Change events in each sheet to trigger activities in each sheet.

A Worksheet_Change event also only works on certain events. If a cell value changes as a result of a formula, this does not trigger the Worksheet_Change.

But the Worksheet_Calculate event would be triggered by a formula that recalculates.

So, if A1 has a formula, you either monitor the precedents of cell A1 with a Change event, or monitor A1 with a Calculate event.

cheers

teylyn,

So, modifying my original Calculate event code posted in the OP works? How do I get that code to monitor three cells instead of just one?

[sorry, I'm not that gifted with vba]

Lawrence

Originally Posted by teylyn

Lawrence,

Take another look at Bob Phillips' code. It's all in there!

cheers

Cool! Will his Intersect below line work with Worksheet_Calculate? If so, then I'm golden!!!

 If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Thanks again,

Lawrence