CODEWITHSUNDEEP
excelvbaeventsexcel-formulavba7
fana it
fana it

Reputation: 63

CHANGE vs CALCULATE in Excel VBA Worksheet event - not triggered by formula values updates

enter image description hereI need to compare two cells: A1 and C1. If A1 <> C1 Then run some code.

A1's content is a formula dependent cell (from another sheet, same workbook). C1's content is static, only changing at the end of the macro run.

Issue: Having an issue with catching Worksheet_Change(ByVal Target As Range) and Worksheet_Calculate() event when the cells contents are changed, as a result of formulas coming from other sheets (same workbook): when A1's content is updated (by formula), event CHANGE nor CALCULATE will catch this change.

Unless I directly key-in in the target sheet any changes, those formula-updated-result cells won't trigger those events, hence not being able to run associated macros 1 and 2.

I have checked out some suggestions from https://stackoverflow.com/search?q=%5Bvba%5D+WORKSHEET_CHANGE&s=ceca4078-9061-4cfb-ae34-f57285b98d7d, but couldn't fix it.

Any ideas or suggestions? Higly appreciated.

  Private Sub Worksheet_Change(ByVal Target As Range)
    
    Set Target = Range("a1")
    
    Dim my_Target1 As Variant
    Dim my_Target2 As Variant
    
    my_Target1 = Cells(1, 1).Value
    my_Target2 = Cells(1, 3).Value
    
    
       If Not my_Target1 = my_Target2 Then
       
                Call macro1
                MsgBox ("end of update routine")
    
        Else: Call macro2
      End If
    
    
    Exit Sub
    End Sub

----- here goes the CALCULATE event code----

Private Sub Worksheet_Calculate() 

Dim my_Target1 As Variant
Dim my_Target2 As Variant

my_Target1 = Cells(1, 1).Value
my_Target2 = Cells(1, 3).Value


   If Not my_Target1 = my_Target2 Then
   
            Call macro1
            MsgBox ("end of update routine")

    Else: Call macro2
  End If


Exit Sub
End Sub

NOTE - I have tried 2 different scenarios: (1) scenario OK (successful), if the formulas are fed by new data that is keyed in directly into the workbook; (2) scenario KO (not successful), if the formulas are fed by new data that is coming from an online external source;

Upvotes: 0

Views: 1156

Answers (1)

fana it
fana it

Reputation: 63

i could capture the event with the Workbook_SheetChange(ByVal Sh As Object, _ ByVal Source As Range)

  • thank you all for your help.

Upvotes: 0

Related Questions