Get address of changed cell

[sumdumgai]

Workbook1 Sheet1 contains a range of cells that contains formulas. These formulas use values of cells in Workbook2 Sheet2; i.e., there is an external data link. Using change or calculate events on Workbook1 Sheet1, is there a way to get the address of the cell on Sheet1 whose value has changed because a cell in Workbook2 Sheet2 has been changed?

Thanks.

[AlphaFrog]

Using change or calculate events on Workbook1 Sheet1, is there a way to get the address of the cell on Sheet1 whose value has changed because a cell in Workbook2 Sheet2 has been changed?

Sorry no. The Worksheet_Change event only triggers on manual changes to the worksheet and not formulaic changes. The Worksheet_Calculate event does not report which cells with formulas were recalculated.

One idea is to have a worksheet_Change procedure in Workbook2_Sheet2 that logs the cell address of any manual change to a special cell in the workbook. Then have an external link in Workbook1 that references that special cell. Then in the Workbook1 WorkSheet_Calculate procedure, check that linked cell to see what manually changed in workbook 2 and you can figure out what formulas were affected. Clear as mud, hungh.

[sumdumgai]

Thanks for the reply. I kind of figured that was the case. I'll have to come up with another way. Your suggestion to place procedure in Workbooks2_Sheet2 would force me to change Workbook2 to xlsm, which I do not want to do.

[Rick Rothstein]

A possibility... maybe. Write a one-use only macro to place the current values for the cells you want to monitor in Comments for those cells. That initializes the worksheet so the Calculate event has starting values to work with. Now, in the Calculate event, loop through the monitored cells and check if the value stored in the Comment is different from the value displayed in the cell... if not, go to the next cell, but if so, report that cell's address to wherever it is you need it and update the Comment for the new value. Alternately, if you don't want to use the cells' Comments, use a hidden mirror worksheet to store the cells values as constants (cell for cell) instead.

[LJMetzger]

Rick was on the right track. You can use the Worksheet_Calculate() event to identify changes if you save the 'starting values' of the cells you want to monitor. In your case InitialzeSheet1SavedValuesForWorksheetCalculate() would look for all cells on Sheet1 whose formulas reference Sheet2.

For a working copy of a similar application see the file associated with post #3 in http://www.excelforum.com/excel-prog...ionchange.html

The code for that file is below.

In the ThisWorkbook code module (Formula changes cells):

Please Login or Register  to view this content.

In the Sheet1 code module:

Please Login or Register  to view this content.

In an ordinary code module:

Please Login or Register  to view this content.

Lewis

[Rick Rothstein]

I see Lewis posted a solution using a "mirror sheet" idea I proposed to hold your formula values. The other suggestion I made was to use the cells Comments to hold those values (which makes the code simpler). If you go this route, you have a choice about how visible to make the Comments. If you do not plan on having any comments in the workbook, I would suggest going into Excel Options, select the "Advanced" option, scroll down to the "Display" section and select "No comments or indicators" in the "For cells with comments, show:" subsection. Then put this macro in a general code module...

Please Login or Register  to view this content.

and this code in the worksheet's code module...

Please Login or Register  to view this content.

To condition your existing data, select the cells you want to monitor and run the MoveValuesToComments macro. You would also do this for any new cells if you ever entered more data that you want to monitor. That's it... the event code will handle the rest although I am thinking you may want to replace its MsgBox code line with code to do whatever you actually wanted to with the cell's address.