need to "read" value not formula

wigarth

New Member
Joined
Apr 16, 2016
Messages
19
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,678
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,934
Office Version
365
Platform
Windows
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
Code:
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
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "D4" And Range("AB6") = 0 Then Call MyMacro
End Sub
 
Last edited:

wigarth

New Member
Joined
Apr 16, 2016
Messages
19
Worksheet calculate worked perfectly. Thanks All of You for the response.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,678
Office Version
365
Platform
Windows
Worksheet calculate worked perfectly. Thanks All of You for the response.
Glad you got something that works for you. Thanks for letting us know.
 

Forum statistics

Threads
1,085,586
Messages
5,384,598
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top