Running macro when a linked cell changes

[LDT]

I have the following problem;

Cell B2 in the worksheet “ODE” in judge1.xlsm is linked to cell B2 in the worksheet “Start” in chiefjudge.xlsm.
I would like the macro “Clear” to run when a change is made to B2 in chiefjudge and this is updated in B2 in judge1.

(I don’t know if it makes a difference but “judge1” is protected)

Any assistance is much appreciated

Thanks

[foxguy]

Hi LDT;

Cell B2 in the worksheet “ODE” in judge1.xlsm is linked to cell B2 in the worksheet “Start” in chiefjudge.xlsm.
I would like the macro “Clear” to run when a change is made to B2 in chiefjudge and this is updated in B2 in judge1.

If I understand right;
You want a macro in chiefjudge to automatically check to see if chiefjudge!B2=judge1!B2, and if they are the same run the "Clear" macro.

If that is correct then this should work.
Put this in "ThisWorkbook" module so it's constantly run when ever someone changes cells.

Please Login or Register  to view this content.

[LDT]

Hi
Thank you so much for trying to help me!
I probably didn’t do a good job in explaining the problem.
I would like the macro Clear to run in judge1 (worksheet ODE) when cell B2 changes value. The problem is that judge1 is protected and cell B2 is linked to cell B2 in chiefjudge (worksheet Start). So I don’t get “normal” macros to work.

(This is for a sporting event, so when the Chief Judge put the name of a new athlete in cell B2, the judges scores (for the previous athlete) should cleared).

[foxguy]

Ok, try again.
The formula in [judge1]ODE!B2 : =[chiefjudge]Start!B2
And you want judge1!Clear() to run when [judge1]ODE!B2 changes (which automatically happens when [/SIZE][chiefjudge]Start!B2 changes).

Assuming that you want it to happen automatically (no button to click), then I can think of 2 ways to handle this.
This is not actual code. This is just describing the options, so you can pick one.

Please Login or Register  to view this content.

In choice 2, the only test I can think of is to store the value in some other cell (hidden maybe), so that when it changes the new value can be compared with the old value.

[LDT]

Thank you so much for helping!

Yes! You’ve understand my problem!

To be honest my macro knowledge is quite limited. The code I have is done by recording and getting code of the net. So I don’t really know witch of your solutions is the best.

I can maybe mention that there are 13 judge files (judge1, judge2, judge3....) that are linked to the chief judge. All of the files are saved on a small server and will be open on different computers at the same time.

[foxguy]

I can maybe mention that there are 13 judge files (judge1, judge2, judge3....) that are linked to the chief judge. All of the files are saved on a small server and will be open on different computers at the same time.

Ok, now I need to know more in order to make a recommendation.
When you change 1 cell in chiefjudge.xls, are more than one judge files changed at the same time?
IOW is this true?
[judge1]ODE!B2 : =[chiefjudge]Start!B2
[judge2]ODE!B2 : =[chiefjudge]Start!B2
or is it more like this:
[judge1]ODE!B2 : =[chiefjudge]Start!B2
[judge2]ODE!B2 : =[chiefjudge]Start!B3

Do the judge files need to be changed immediately when you change a cell in chiefjudge or can it wait until later?

Is the macro that needs to be run in the judge files all named "Clear"?

I think that's all I need to know.

[LDT]

All the judges files is linked to the same cell
[judge1]ODE!B2 : =[chiefjudge]Start!B2
[judge2]ODE!B2 : =[chiefjudge]Start!B2

The cell B2 in the judges files is updated by a “update” macro witch automatic run every 15 seconds. All the judges files are the same and the links from judge to chiefjudge files are the same (the links from the chiefjudge file to judge is different (it collects all the different judges scores))

If it’s possible the macro clear should run automatic within a few seconds after cell B2 is updated.

[foxguy]

The cell B2 in the judges files is updated by a “update” macro witch automatic run every 15 seconds. All the judges files are the same and the links from judge to chiefjudge files are the same (the links from the chiefjudge file to judge is different (it collects all the different judges scores))

All right, I got confused again. "update" is in the judge files? And it runs every 15 seconds? So what is "Clear" for?
If "update" is updating B2 every 15 seconds then just

Please Login or Register  to view this content.

Or did you mean that [chiefjudge]Start!B2 is updated every 15 seconds?

[LDT]

After working with this problem for a week I was able to send the files to a programer, the code he made was:

Please Login or Register  to view this content.

and it works!

Thank you so much for your help!