Value change calls proceedure. One for the gurus?

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
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? :confused:
I really appreciate the help! TTom :cool:


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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,129
Messages
5,857,537
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top