Value change calls proceedure. One for the gurus?

[TTom]

A bit of text, but it should be a quick read as to what I need assistance with.
I suspect I need to use on on event change or something, but not quite sure how?
I really appreciate the help! TTom


EXAMPLE:
I make an entry in Sheet4, this results in a change in value in Sheet1.Range("F20").
When this occurs I need code that will notice that Sheet1.Range("F20") has indeed changed,
find the value from Sheet1.Range("A20") and assign it the variable 'myRef'.
Call 'myProceedure' in Module1 and make available the string value from 'myRef' for use.

SETUP:
-- Sheet1, Column A: contains reference codes as string value.
-- Sheet1, any Row: the string value in Column A is related to the formula result in Column F.
-- Sheet1, Column F: contains formulas that change displayed numeric value when certain entries are made on other sheets.
-- Sheet1, Column F: contains a named range.
-- Sub MyProceedure() in Module 1 is used to get data from Sheet1.

PROBLEM TO RESOLVE:
When any value changes via formula in a named range in Sheet1, Column F, as a result of change in any number of other sheets, then:
a) determine location of what Cell (or Row) 'value' changed in Sheet1, Column F as result of change on another Sheet.
b) find value of cell in column A of that same row equal to a variable (value of cell = myRef)
c) call Find_mySum proceedure, make available the variable 'myRef' just assigned for this procedure call.

NOTE:
I WILL need to access Sheet1 for viewing , sorting, and other task without having macro proceedure run.
Of course, the macro should run anytime a value changes within specified range on Sheet1.

 

[Damon Ostrander]

Hi TTom,

I don't believe this can be done exactly as stated. The problem is that there is no event triggered by a cell changing due to calculation. If you use the Worksheet_Calculate event itself, then you have to save all the values in the range and each time the worksheet calculates check to see if any have changed. This in itself is not hard to do, but since it runs whenever the sheet calculates it violates your requirement that the code not run when you do other tasks on the worksheet, but only when cells in the monitored range change. A workaround for this would be to turn off automtic worksheet calculation or to disable worksheet events whenever you want to do other work on the sheet--and this could be implemented via a button click or keyboard shortcut--but this may not be a solution that meets your particular need.

If you want the solution based on the Calculate event, let me know.

Damon