VBA - Event/Change Macro on Cell Value using a Formula

[imcfcl]

I hear this can be very difficult but I want to be having a macro run automatically depending on the contents of a cell, the cell includes a formula. I did find a working example somewhere, but for the life of me I can't find it again - anyway to the point

When I mess with the Width and Height, it changes the spec. In Cell Q25, I have a little OR formula for when the is Spec J and K to give TRUE and FALSE. My code is to run A macro if Q25 changes from True to False, and visa versa. Unfortunately nothing is happening.

Any help would be much appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
 
 If Not Intersect(Target, Me.Range("Q25")) Is Nothing Then
 With Target
 
 If .Value = True Then
 Application.Run "'Tube Motor Matrix - Master.xls'!Show27"
 
 Else
 
 If .Value = False Then
 Application.Run "'Tube Motor Matrix - Master.xls'!Hide27"
 
 End If
 End With
 End If
 
ws_exit:
 Application.EnableEvents = True
 End Sub

 

[hatstand]

I changed the bottom lines to

End If
End If
End With

ws_exit:
Application.EnableEvents = True
End If
End Sub

Strange, because until I had closed down all excel workbooks and reopend the test one I made the event wouldn't fire. It does now though.

 

[Andrew Poulsom]

The Worksheet_Change event fires when a cell is changed by the user. It doesn't fire when the result of a formula changes. You can test for a change in the precedent cells (if they are modified by the user) or you can use the Worksheet_Calculate event procedure, which will fire each time the worksheet is calculated.

 

[hatstand]

Another way would be to put this in your sheet code. Replace the MsgBox for what you want to happen:

Private Sub Worksheet_Calculate()
IfConMet
End Sub
Public Sub IfConMet()

If Range("A1").Value = True Then

MsgBox "Yip"
Else
MsgBox "No"
End If

End Sub