named ranges at workbook and worksheet levels

[mark kubicki]

this i should know, but never get right...
a range can be named at a workBOOK or workSHEET level (true?)


....this presumes that a name could either exist at the workbook level and
have the same value on all of the worksheets
or
or the same name could exist on each worksheet and have a value specific to
that sheet
(is this correct?)


i use this to define a name at the workbook level:
ActiveWorkbook.Names.Add
Name:="SuspendProgrammedAutomaticCalculation", _
RefersToR1C1:="=""True"""

what would the correct code be for creating a worksheet level name


thanks in advance
mark

 

[Jack Schitt]

I just turned on the macro recorder, created a local name that refers to
Sheet1!$A$1, switched off the recorder and reviewed the code which showed:

ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", _
RefersToR1C1:= "=Sheet1!R1C1"

So it seems that you just have to specify the worksheet name in front of the
range name, in order to localise it to that sheet.

 

[Jack Schitt]

This also works:

ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="MyRange", _
RefersToR1C1:="=Sheet1!R1C1"

 

[mark kubicki]

currently i use this method:
If [FeeReviewAtTimeEntry] = "True" Then...(where
"FeeReviewAtTimeEntry" is a named range)
which is referring to a workbook level name;
how would i refer to a name on a worksheet other than the active one?

would i need to refer to the 2 types of names differently?

 

[Jack Schitt]

A bit out of my depth. If I get it wrong someone will pounce. But it seems
to me that if Sheet1 is the activesheet, and the range FeeReviewAtTimeEntry
is local to sheet2, then
If [Sheet2!FeeReviewAtTimeEntry] = "True" Then etc

--
Return email address is not as DEEP as it appears

currently i use this method:
If [FeeReviewAtTimeEntry] = "True" Then...(where
"FeeReviewAtTimeEntry" is a named range)
which is referring to a workbook level name;
how would i refer to a name on a worksheet other than the active one?

would i need to refer to the 2 types of names differently?

 

[mark kubicki]

thanks...
(a wise old fool once said: "check the obvious first..."; how obvious...)

A bit out of my depth. If I get it wrong someone will pounce. But it seems
to me that if Sheet1 is the activesheet, and the range FeeReviewAtTimeEntry
is local to sheet2, then
If [Sheet2!FeeReviewAtTimeEntry] = "True" Then etc

--
Return email address is not as DEEP as it appears

currently i use this method:
If [FeeReviewAtTimeEntry] = "True" Then...(where
"FeeReviewAtTimeEntry" is a named range)
which is referring to a workbook level name;
how would i refer to a name on a worksheet other than the active one?

would i need to refer to the 2 types of names differently?

This also works:

ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="MyRange", _
RefersToR1C1:="=Sheet1!R1C1"

--
Return email address is not as DEEP as it appears
I just turned on the macro recorder, created a local name that refers to
Sheet1!$A$1, switched off the recorder and reviewed the code which showed:

ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", _
RefersToR1C1:= "=Sheet1!R1C1"

So it seems that you just have to specify the worksheet name in front
of
the range name, in order to localise it to that sheet.

--
Return email address is not as DEEP as it appears
this i should know, but never get right...
a range can be named at a workBOOK or workSHEET level (true?)


...this presumes that a name could either exist at the workbook

level
and

have the same value on all of the worksheets
or
or the same name could exist on each worksheet and have a value specific
to
that sheet
(is this correct?)


i use this to define a name at the workbook level:
ActiveWorkbook.Names.Add
Name:="SuspendProgrammedAutomaticCalculation", _
RefersToR1C1:="=""True"""

what would the correct code be for creating a worksheet level name


thanks in advance
mark