need to "read" value not formula

[wigarth]

Hi!

Would really appreciate some help on this.

I have a sheet (sheet1) with the cell «AB6» This Cell has a quite big formula to calculate values from multiple sheets and it works great.
I have a macro to trigger if the cell value of AB6 =1

This macro is a “Private Sub Worksheet_Change(ByVal Target As Range)” kind of macro that is placed in the sheet1 page

I think the Macro somehow reads the formula and not the value of AB6, and therefore jinxingmy setup.

If I manuallyput 1 in “AB6” the macro starts running, but when the formula returns “1” it does not trigger. (No decimals here to make this fail btw)
Any quickfix to this? Or some formula I can put in another cell to make my macrounderstand this more easy?

Thanks inadvance for any help.

Kind reggards:
Wigarth

 

[footoo]

Use : Private Sub Worksheet_Calculate()

 

[Peter_SSs]

A Worksheet_Change event is never triggered by a formula cell changing its value. Your AB6 always contains the same formula so the Worksheet_Change event does not see that cell a ever changing (unless you change the actual formula in it).

Options are to
- use the Worksheet_Calculate event (& check for 1 in AB6) but, depending on your circumstances, may mean that the code is triggered much more often that you would like.
- find all precedents of AB6 that do change actual values, not by formula, and put Worksheet_Change events on that/those worksheet/s.

 

[Yongle]

A change in value of AB6 is NOT the correct trigger
- AB6 changes when the value in another cell (cell X) changes
- the correct trigger is change in cell X

AB6 " has a quite big formula to calculate values from multiple sheets"
- there are many "triggers" in many sheets

Consider triggerring your macro when sheet1 is activated
- place in sheet1 sheet module

Private Sub Worksheet_Activate()
    If Range("AB6") = 0 Then Call MyMacro
End Sub

Private Sub MyMacro()
    MsgBox "Hello"
End Sub

If formula in AB6 refers to any cells (in sheet1) which are amended manually then Worsheet_Change is also required
- place in sheet1 sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "D4" And Range("AB6") = 0 Then Call MyMacro
End Sub

 

[wigarth]

Worksheet calculate worked perfectly. Thanks All of You for the response.

 

[Peter_SSs]

Worksheet calculate worked perfectly. Thanks All of You for the response.

Glad you got something that works for you. Thanks for letting us know.