Call a macro based on cell value changes but formula stays the same

Question

I need to call a macro when cell B3 changes, however B3 is an RTD link so when the value changes the formula is still the same and excel doesn't recognise the change. How can I get my macro to run when the value changes but the formula doesn't?

I've tried using a simple Range("B3").Value but that gives me a run-time error "424". I then tried creating a range object to get around this but still got another error message.

Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B3")) Is Nothing Then
    Call Copy_Values
    End If
End Sub

I'm relatively new to VBA so I know I could be missing something obvious, thanks for any help you can provide

Unknown Unknown · Accepted Answer · 2019-02-03T18:05:51

You want Worksheet_Calculate not Worksheet_Change. Use a static var to 'remember' the value from the last calculation cycle.

Sub Worksheet_Calculate()

    static beeThree as variant

    If Range("B3").value2 <> beeThree Then
        application.enableevents = false
        application.calculation = xlcalculationmanual
        Copy_Values
        beeThree = Range("B3").value2
        application.calculation = xlcalculationautomatic
        application.enableevents = true
    End If

End Sub

Call a macro based on cell value changes but formula stays the same

1 Answers