×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Auto Run Macro

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

RE: Auto Run Macro

From Excel's help:

Quote (Help for Worksheet Change Event)

This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

RE: Auto Run Macro

Taking MintJulep's contribution a bit further ...

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

Yes, I think Denial's approach is the only way to check if a specific cell's result has changed by the calculate event.

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

(OP)
MintJuelep

It took a few minutes to figure out how to write it, but it works. One Star for you!

David

RE: Auto Run Macro

(OP)
All,

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

(OP)
For what it's worth I have 81 rows of inputs on sheet 1. There are another 10 sheets performing dynamic calculations based on the 100+ inputs on sheet 1. The worst calculation sheet (named friction) starts on cell A1 and ends on cell EK310. Through the calculations I must predict rotating speed, but most of the dynamic calculations are based on speed. I need to copy - paste a static predicted speed so I don't have a circular calculation from my dynamic speed calculation.

I'm sure this sounds a little strange, but it works.

Thanks again.

RE: Auto Run Macro

Sounds like Goal Seek or Solver could be useful.

RE: Auto Run Macro

If it will always converge, go into Options|Formulas and Enable Iterative Calculation. Makes many things possible that would otherwise result in circular reference errors or require macros.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources