Auto Run Macro
Auto Run Macro
(OP)
All,
I need to run a macro anytime a cell value changes, but the cell is a formula, not just a static value. I have tried the following code and it works fine if I manually enter a value in the target cell, but if I use a formula in the target cell it does not work. Is there a way to run the macro when a formula result changes? In my file cell AD61 is the sum of several cells.
Example of “Does Not Work Code”
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("AD61")) Is Nothing Then Speed
Application.EnableEvents = True
End Sub
Sub Speed()
Dim S As Double 'Speed RPM
S = Range("AD70")
Range("AD71").FormulaR1C1 = S
End Sub
Thanks
David
I need to run a macro anytime a cell value changes, but the cell is a formula, not just a static value. I have tried the following code and it works fine if I manually enter a value in the target cell, but if I use a formula in the target cell it does not work. Is there a way to run the macro when a formula result changes? In my file cell AD61 is the sum of several cells.
Example of “Does Not Work Code”
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("AD61")) Is Nothing Then Speed
Application.EnableEvents = True
End Sub
Sub Speed()
Dim S As Double 'Speed RPM
S = Range("AD70")
Range("AD71").FormulaR1C1 = S
End Sub
Thanks
David





RE: Auto Run Macro
RE: Auto Run Macro
You have this cell, let's call it Watched, and you want to run some VBA whenever the value of Watched's formula changes. Pick some spare cell somewhere on your worksheet well away from the action (or perhaps even on its own worksheet), and let's call this cell Copied. Now in your Worksheet_Calculate event handler do the following:
If the value of Watched does not equal the value of Copied then
Run whatever code you have in mind
Set Copied equal to Watched
End If
If you don't like the idea of using a cell for Copied, you could probably use a VBA global variable instead.
RE: Auto Run Macro
You might want to bring this question over to Tek-Tips VBA Forum.
Your Speed function could be simplified and cleaned up:
Sub Speed()
Range("AD71").value = Range("AD70").value
End Sub
RE: Auto Run Macro
It took a few minutes to figure out how to write it, but it works. One Star for you!
David
RE: Auto Run Macro
This is what I have and it is working:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Call Speed
Application.EnableEvents = True
End Sub
RE: Auto Run Macro
I'm sure this sounds a little strange, but it works.
Thanks again.
RE: Auto Run Macro
RE: Auto Run Macro