Workbook Defined Name not available to other sheets

[foxguy]

Hi all;

Are Defined Names attached to the workbook only available to macros when the Active sheet is the one in the Refers To?

I have a Defined Name attached to the workbook (not to a sheet).
Name : Strengths
Refers To : =Data!$P$1


When I'm in VBE with no macro running and active sheet is not Data I type ? Range("Strengths").Address in the immediate window and it correctly prints $P$1.

Now I put a break in Worksheet_Change for every sheet. When it breaks I type ? Range("Strengths").Address in the immediate window, I get error 1004. Except in Data!Worksheet_Change. Then it correctly prints $P$1.

How can I make a Defined Name attached to the workbook available to all macros?

[romperstomper]

In a worksheet's code module, an unqualified reference to Range refers specifically to a range on the sheet containing the code. If the range is not on that sheet, then you'll get an error.

[Richard Buttrey]

Hi,

I think you need to specify the sheet name when using sheet events. i.e.

Please Login or Register  to view this content.

HTH

[foxguy]

So how can I refer to a name that is attached to the workbook from withing a macro?
The only thing I can think of is to define a name for each sheet with the Refers To = the workbook name.

[romperstomper]

Either qualify the Range call with the correct sheet, or use

Please Login or Register  to view this content.

If you know your workbook is active, you can also use

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[foxguy]

Thanks romperstomper;

application.range("blah") makes sense. Don't know why I didn't think of it.

[romperstomper]

If you want to be safe, I'd use one of the first two options (I've just amended my code as my phone knocked off the RefersToRange part in the first code snippet).

[foxguy]

Yeah, I don't use the 3rd option. I'm working on the forum so much, I don't use shortcuts like that any more. The people I help don't know what they mean. It's just easier to use the code that they understand.

[romperstomper]

Just to clarify, when I say the first two, I mean these:

Either qualify the Range call with the correct sheet, or use

Please Login or Register  to view this content.

[foxguy]

Hi romperstomper;

Now you lost me. You say first 2, but only 1 is posted.

[romperstomper]

It's an either or statement - either specify the sheet, or use the names collection.