Worksheet Change between two different sheets

[Zenarte]

Hello, folks. I've created a macro and I want it to run whenever the value in this cell changes. Well, as an example, I'll take the range A1 for instance, so if I write anything in there, my macro will automatically run. I already have this code, except that it only works if the contents are on the same sheet:

SHEET 1

Please Login or Register  to view this content.

Ok, now what if, for instance, in SHEET 2, A1 cell, I decide to use the following formula:

Please Login or Register  to view this content.

Well, even if I apply that same Worksheet_Change code in SHEET 2, that won't really make any difference, after all, although the displayed value will be changed, Excel is gonna say: "Okay buddy, but the formula is still there, that hasn't really changed anything".

I'd like to know if there's any code that I could use in that same config, in other words, have the value in A1 linked in both sheets, and whenever I change it in SHEET1, the macro will run in my SHEET2. I didn't want to take this turn, but the layout of a sheet doesn't match with the other. Thanks in advance ~

[shg]

There is no change event generated when cells change as the result of a formula.

You could run code in the Calculate event, but it needs to be very efficient code, and if it changes anything in Excel it will kill Undo.

[Zenarte]

Hmm I see, thanks for the feedback shg ~

In that case, I was actually thinking that whenever I change the value in A1, I can automatically run a macro where I copy the contents of that cell and paste in the other one. I guess that one will do?

[Zenarte]

... Except that the value in A1 happens to be a validation list cell, and whenever I select anything in there, it doesn't trigger any change

[shg]

Data validation of any type doesn't affect triggering the Change event.

[Zenarte]

Hmm... I've found this code while I was searching for answers, except that it only seems to work for a specific amount of choices. Wouldn't there be any way to expand it to any given possibility that is chosen? I mean, whatever value that is selected on the validation list, the macro will run? If it's a no go, I think I'll just give up of this idea. Thanks again, shg ~

Please Login or Register  to view this content.

[Zenarte]

Oh... I think I've got it! Thanks for your help, shg!

Sheet 1

Please Login or Register  to view this content.

MacroCopy

Please Login or Register  to view this content.

[shg]

Please Login or Register  to view this content.

But why use code instead of a simple formula in Sheet2!A1?

[Zenarte]

... And that, ladies and gentlemen, it's the difference between a VBA newbie and a Forum Guru haha

Thanks a bunch, that solution worked perfectly!

[shg]

You're welcome.