Run Macro Based On Worksheet Change (Cell Contains Formula)

2
votes

I think I have a fairly reasonable/standard ask and the methods I've tried thus far have proved unsuccessful.

I'm running Excel 2013 32-Bit and in Sheet 6 in Cell J7 I have a third-party formula that changes every so often. Basically I'd like to call a macro every time that cell value changes.

Keeping in mind that this cell is a formula (I've played around with changing this third party formula to a straightforward formula such as A1+B1 and it hasn't changed the behaviour).

Basically Excel does not call my macro and recognize the change in this cell unless I manually type over the formula or press F2 in the Excel cell and hit Enter or if I click into the Formula box and press enter (all steps require a user click).

Here are the methods I've tried (all are a variation of Worksheet Change or Worksheet Calculation). If there is something glaring I'm missing or a method I have not tried please advise, thanks.

https://www.extendoffice.com/documents/excel/4423-excel-run-macro-on-cell-change.html

http://www.dbforums.com/showthread.php?1661517-Execute-Macros-From-Formulas-(Or-Cell-Value)

How can I run a VBA code each time a cell get is value changed by a formula?

https://www.linkedin.com/pulse/20140623100408-92734594-how-to-execute-excel-vba-macro-on-change-of-specific-cell-in-excel-sheet

https://www.excelforum.com/excel-programming-vba-macros/400984-change-in-cell-from-formula-to-auto-run-a-macro.html

http://www.ozgrid.com/forum/showthread.php?t=144429

Run VBA Script When Cell Value Change by Formula

VBA code doesn't run when cell is changed by a formula

Trigger macro when any cell containing formula changes

automatically execute an Excel macro on a cell change

1
vbaexcelbyval
What is this third-party formula ? – Plagon
You'll need a global variable to store the value of Sheet6, cell J7, can populate this global variable on workbook open. And then in the worksheet_calculate event, have a check to see if the value of that cell is different from the value of your global variable. If they are different, then the formula calculation has yielded a new result and the rest of your code can trigger, otherwise don't run the rest of your code. It would be easier to troubleshoot if you posted sample data and your current code attempt. – tigeravatar
Hi @UGP it is an SAP formula. – user979226
Hi Tigeravatar, please see the attached file. – user979226
I dont know much about SAP, but have you tried linking another Cell to the Cell with the Formula? If that ones updating it could trigger the Worksheet_Calculate Event – Plagon

1 Answers

0
votes

So it looks like this third party add-in has an event that I could use to provide the same end goal. It does not activate on that worksheet change, but it provides the solution I'm looking for.

It is an AFTER_REFRESH event, meaning when a REFRESH is triggered via the third-party ribbon it will run my vba as I wanted to happen.

Thanks all for your thoughts/input.