2

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

YowE3K
  • 23,852
  • 7
  • 26
  • 40
user979226
  • 47
  • 2
  • 11
  • 3
    What is this `third-party formula` ? – Plagon Jul 11 '17 at 12:20
  • 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 Jul 11 '17 at 13:46
  • Hi @UGP it is an SAP formula. – user979226 Jul 11 '17 at 14:14
  • Hi Tigeravatar, please see the attached file. – user979226 Jul 11 '17 at 14:14
  • 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 Jul 11 '17 at 14:36
  • Please see the attached file. It shows the third-party formula and basically when a user makes a change in a third-party add-in in Excel it makes the change in cell Sheet2.A1 which should impact Sheet1.A1. However right now what happens is Sheet1.A1 stays the same and then I have to force a recalc (F2+Enter in that cell) and only then does it recognize a change and trigger my macro. Alternatively I added a macro to force a recalculate of that cell every second, and although this does update the value in that formula cell, it still does not trigger my change unless I F2+Enter in that cell. – user979226 Jul 11 '17 at 14:47
  • https://drive.google.com/file/d/0B862OPB6fp6PWHZiUXRNRjdWSjQ/view?usp=sharing – user979226 Jul 11 '17 at 14:49
  • @UGP that's actually the first thing I tried, if you look at the attached workbook that's there. It did not work unfortunately. – user979226 Jul 11 '17 at 14:50

1 Answers1

0

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.

user979226
  • 47
  • 2
  • 11