Run VBA Macro in Excel when a particular formula recalculates

Question

I am far from great at macros in Excel and could really use some guidance. Need to run a simple macro that looks like this:

Public Sub CopyReplaceValue(rng1 As String) Dim str1 As String Dim str2 As String Dim str3 As String Dim rng2 As Integer On Error GoTo LetsContinue str1 = "N" ''this is your FRZ column str2 = Range(rng1).Row ''Row number of updated cell str3 = str1 & str2 ''FRZ cell to be changed rng2 = Range(str3).Value If Sgn(Range(rng1)) <> Sgn(rng2) Then Range(str3).Value = 0 ElseIf Abs(Range(rng1).Value) <= Abs(rng2) Then Range(str3).Value = Range(rng1).Value End If LetsContinue: Exit Sub End Sub

Now the tricky part is how to call this macro when I need it to be. I want rng1 to be a cell from a range set by me, I then need an event to trigger the call.

The problem is that using Private Sub Worksheet_Change(ByVal Target As Range) does not seem to work since the finished sheet will only change when RTD links and formulas update. That event only seems to update when I manually change a value in the range, which isn't something that I will ever do when the sheet is complete.

Is there a way to call this macro when a cell within this range re-calculates and/or changes via formula, without a person touching the sheet.

brunohdaniel · Accepted Answer · 2017-08-16 19:40:19Z

1

Try to use the Private Sub Worksheet_Calculate().

You may need to create some to trigger or not your macro inside the function.

answered Aug 16, 2017 at 19:40

brunohdaniel

6225 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Craig Key Over a year ago

Sorry, I should have mentioned that I have tried using that but so far can't come up with a way to keep it from ending up in an endless loop.

YowE3K Over a year ago

@CraigKey You would need to set Application.EnableEvents = False at the start of your code (and reset it to True at the end). That will stop any events firing while your code is running (i.e. a calculation due to changes made by your code won't re-fire the Worksheet_Calculate event.)

Collectives™ on Stack Overflow

Run VBA Macro in Excel when a particular formula recalculates

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related