Worksheet Change ByVal Formulated Value Change

[rickincanada]

Hi there I'm struggling with the following problem and hoping that someone can provide me with some guidance.

I'm attempting to use the Worksheet_Change(ByVal Target As Range) to call a macro when Target.Address = "$F$25" however the problem is that F25 is a formulated value. Therefore I'm wanting the macro to run when other cells are modified that effect the value in F25. The reason for this is I have a series of check boxes that effect the value in this cell, therefore using the Change ByVal on the check box cells doesn't work. The trouble is that even when the value in F25 changes the macro doesn't run as the formula in F25 hasn't been modified. Is this possible?

Thanks for any help you can provide.

 

[VoG]

Try using

Private Sub Worksheet_Calculate()

This does not have a Target parameter so you''ll have to check F12 specifically like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldVal As Variant
If Range("F12").Value <> OldVal Then
    OldVal = Range("F12").Value
    '
    'your code here
    '
End If
End Sub

 

[Andrew Poulsom]

Can't you test the precedent cell(s) that contain constants instead of F25?

 

[rickincanada]

Hi Andrew - the challenge is that I'm using a different worksheet to record the precedent cells with constants (I assume you're referring to the TRUE/FALSE component of the Check Box). Therefore when the box is checked on Sheet1 it sets Sheet2 A1 to TRUE which does not trigger my Worksheet Change event on Sheet1 to fire. If that made any sense.

 

[Andrew Poulsom]

Put the event code in the worksheet that contains the precedent cell(s).

 

[rickincanada]

I've tried that as well however because the code I'm calling inside the event is an advanced filter is requires the appropriate sheet to be selected...somewhat laborious.

I've used VoG's suggestion and it works perfectly. Thanks so much for the help guys!