Macro that automatically executes when cell value changes

[Koani]

I am trying to have a specified macro run whenever cell B19 equals one. I tried using Worksheet_Calculate but the problem is that when the macro is run, it changes cells (other than B19) which causes the macro to start over. (One of the first actions of the macro is to enter a new row at 25)

Thoughts?

 

[Joe4]

Welcome to the Board!

Place an IF statement in your code to limit it to just that cell, i.e.

If Target.Address=Range("B19").Address Then
...

 

[Smitty]

Welcome to the Board!

If B19 is the result of a calculation then the Calculate event is what you want.

If the code is going into a recursive loop you can add Application.EnableEvents = False at the beginning of the code, just make sure to set it back to True at the end.

HTH,

 

[VoG]

Try something like

Private Sub Worksheet_Calculate()
If Range("B19").Value = 1 Then
    Application.EnableEvents = False
    Call MyMacro
    Application.EnableEvents = True
End If
End Sub

 

[doofusboy]

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Application.Intersect(Target, Range("B19")) Is Nothing Then
        If Range("B19").Value = 1
        Call your_macro
        Exit Sub
        End IF
    End If
End Sub

 

[Joe4]

Scratch my reply. Using TARGET works well with the Worksheet_Change event, but I don't think it applies here with the Worksheet_Calculate event (time to get those eyes checked! ).

 

[Koani]

Thank you so much for all your help! I've been able to get the file to work as long as I have the worksheet open. Is there a way so that the macro can execute in the background (if I I'm not viewing the specific worksheet, or workbook - the target workbook will be open)?

 

[Smitty]

You'd have to put code in whatever workbook you're in and tell it to run code in the other one. Event code isn't going to run in a deactivated workbook.