Worksheet Change Event - Multiple Sheets Involved

[alaskanpilot]

Hi everyone,

I've found a few code snippets dealing with calling a Macro whenever a certain cell changes, but I cannot get any of them to work for me. I am wondering if my situation is a little different.

I have this code right now, placed into Sheet4:

Private Sub Worksheet_Change(ByVal Target As Range)
    
Application.EnableEvents = False

    If Not Intersect(Target, Target.Worksheet.Range("W12")) Is Nothing Then LoadsheetConvert

Application.EnableEvents = True
  
End Sub

I need the macro "LoadsheetConvert" to fire whenever cell W12 on Sheet4 has its value change. Cell W12 contains a formula, so its contents never change but it's value will. The formula in W12 refers to a named range. That formula, in case it matters, is

=LoadsheetChoice

which refers to a cell located on Sheet2.

Am I having issues because part of this process spills into another sheet? Is Excel perhaps not detecting changes in W12 because the formula in W12 does not change? I know that the macro I'm trying to call, is working, since I can manually make it run correctly. I've tried commenting out the EnableEvents lines to see if that was an issue, but it made no difference. I'm just puzzled here.

Thanks!

 

[Jonmo1]

Yes, the problem is that the Worksheet_Change code is triggered by changing the contents of a cell, not the result of a formula.

Since the formula in W12 is =LoadsheetChoice which is a named range for a cell on Sheet2,
And that is the cell that actually changes,
Then Put the code on Sheet2, and change the W12 in that code to whatever cell the LoadsheetChoice named range refers to.

Hope that helps.

 

[alaskanpilot]

Yes, the problem is that the Worksheet_Change code is triggered by changing the contents of a cell, not the result of a formula.

Since the formula in W12 is =LoadsheetChoice which is a named range for a cell on Sheet2,
And that is the cell that actually changes,
Then Put the code on Sheet2, and change the W12 in that code to whatever cell the LoadsheetChoice named range refers to.

Hope that helps.

Thanks for the help. It did help simplify things a little, in that I no longer need the old cell W12 because I linked instead to its source in Sheet2. But here we have the same problem again. the cell referred to by LoadsheetChoice has its own formula, which derives its result from a CHOOSE function:

loadsheetChoice refers to C26 on sheet2, which contains this:

=CHOOSE(MATCH(tailNo,tailList,0),F3,F4,F5,F6,F7,F8,F9,F10)

This returns a value based on which "tailNo" is active. There are only two different possible values that can be returned. Now, tailNo is determined by a drop-down data validation setup which is on yet another sheet (Sheet 6, cell V7). I had considered setting this particular cell on Sheet 6, with the dropdown list, to be the target cell. But the problem there is that changing the tailNo only sometimes results in a change to LoadsheetChoice. That's why I created the CHOOSE setup, to help determine whether a change to the tailNo results in a change to LoadsheetChoice. Now that I think about it, maybe there's a way to code that check into the Change Event code.

I'm sure I've overcomplicated this thing, but it's sure a great way to teach myself a few new tricks along the way as I do my research and ask questions of the others here. I'll keep plugging away at this, and remain open to any other ideas.

 

[Jonmo1]

Well, you have 2 choices.

1. Use the WorkSheet_Calculate event instead of Change.
This WILL be triggered by changes to the result of a formula.
However, there is no Target. Meaning you can't know WHICH formula changed.
So the code will run EVERY single time a calculation is triggered on the sheet.
So you may need to do something like
If Range("C6").Value = "xx" Then Do this

2. Continue using the Worksheet_Change event, which you can trap which cell changed.
But you need to track down the end of the chain of formulas.
Which cell(s) are actually manually changed that cause the formula in C6 to change it's value.
Then that (or those) cell(s) are the one(s) you need to trap with your Intersect Statement.