CODEWITHSUNDEEP
vbaexcel
Craig Key
Craig Key

Reputation: 151

Run VBA Macro in Excel when a particular formula recalculates

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.

Upvotes: 0

Views: 464

Answers (1)

brunohdaniel
brunohdaniel

Reputation: 622

Try to use the Private Sub Worksheet_Calculate().

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

Upvotes: 1

Related Questions