range name define on non active(focused) sheet

[ell]

VBA Code range name define on other sheet

I use more than one sheet,
the focus is on sheet1
how I can , define a range name (with Names object let say) on sheet2
while the focus ,means active screen, is on sheet1.

I got an error that the application object of range name (in sheet2)
is not defined...
if I make active sheet as sheet2 , it works correctly

any help will be appreciated

EL

 

[Per Jessen]

As always, you should post your code for comments.

Try something like this:

ThisWorkbook.Names.Add Name:="TestRng",
RefersTo:=Worksheets("Sheet2").Range("A1")

Regards,
Per

 

[Don Guillett Excel MVP]

As always, you should post your code for comments.

Try something like this:

ThisWorkbook.Names.Add Name:="TestRng",
RefersTo:=Worksheets("Sheet2").Range("A1")

Regards,
Per







- Show quoted text -

Or,
sheets("sheet2").range("a1").name="Don"

 

[ell]

Or,
sheets("sheet2").range("a1").name="Don"

Hi Julliet
the code:
sheets("sheet2").range("a1").name="Don"
is working correctly if I start from sheet2 (from command button on
sheet2 let say)
but from command button in sheet1 ,where the active sheet is sheet1,
from my tests, I noticed
error message on object application...

 

[GS]

ell brought next idea :

Hi Julliet
the code:
sheets("sheet2").range("a1").name="Don"
is working correctly if I start from sheet2 (from command button on
sheet2 let say)
but from command button in sheet1 ,where the active sheet is sheet1,
from my tests, I noticed
error message on object application...

First thing I'll say here is that unless there's a specific reason to
create global names (ie: workbook level) all defined names should be
made local (ie: sheet level). This removes any/all ambiguity about
defined names withing a workbook. It also allows using duplicate names
on other sheets withing the same workbook.

If you open the defined name dialog while Sheet2 is active, you will
not see the sheetname to the right in the list. If the sheetname isn't
listed to the right then any name missing that are global (workbook
level) So.., to achieve this with naming 'A1' on 'Sheet2' it should
be...

Sheets("Sheet2").Range("A1").Name = "'Sheet2'!Don"

**Note that the sheetname is wrapped in apostrophes followed by the
exclamation character. So the syntax for defining local names is...

'sheet name'!DefinedName


Now when you open the defined name dialog you'll see 'Sheet2' to the
right of 'Don' in the list. (To get rid of the global name 'Don',
select another sheet and delete it in the defined name dialog)

Now when your buttons ref the range by the name 'Don' it will ref cell
'A1' on the sheet that contains the button.

HTH

 

[ell]

ell brought next idea :


First thing I'll say here is that unless there's a specific reason to
create global names (ie: workbook level) all defined names should be
made local (ie: sheet level). This removes any/all ambiguity about
defined names withing a workbook. It also allows using duplicate names
on other sheets withing the same workbook.

If you open the defined name dialog while Sheet2 is active, you will
not see the sheetname to the right in the list. If the sheetname isn't
listed to the right then any name missing that are global (workbook
level) So.., to achieve this with naming 'A1' on 'Sheet2' it should
be...

  Sheets("Sheet2").Range("A1").Name = "'Sheet2'!Don"

  **Note that the sheetname is wrapped in apostrophes followed by the
exclamation character. So the syntax for defining local names is...

   'sheet name'!DefinedName

Now when you open the defined name dialog you'll see 'Sheet2' to the
right of 'Don' in the list. (To get rid of the global name 'Don',
select another sheet and delete it in the defined name dialog)

Now when your buttons ref the range by the name 'Don' it will ref cell
'A1' on the sheet that contains the button.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

you completely right
the Sheet2!Don actually is giving Sheet1!don even I make full
reference to the Names.. .ReferenceTo=Worksheets("Sheet2")....

 

[GS]

ell has brought this to us :

you completely right
the Sheet2!Don actually is giving Sheet1!don even I make full
reference to the Names.. .ReferenceTo=Worksheets("Sheet2")....

If you want to refer to the defined name 'Don' on Sheet1 FROM Sheet2
then your formula or code must ref the sheetname followed by the
defined name as I showed.

To ref Sheet1!Don from Sheet2:
Sheet2 Formula ref is: ='Sheet1'!Don
Sheet2 Code ref is: = Sheets("Sheet1").Range("Don").Value

 

[Dave Peterson]

Instead of specifying the sheetname twice, I use something like:

With Worksheets("sheet 2")
.Range("A1").Name = "'" & .Name & "'!Don"
End With

 

[GS]

Dave Peterson explained :

Instead of specifying the sheetname twice, I use something like:

With Worksheets("sheet 2")
.Range("A1").Name = "'" & .Name & "'!Don"
End With

Hi Dave,
While I agree with you in general, I posted what I posted because my
point was to give the OP the correct way to define a local name. Your
suggestion does exactly that but requires a bit of study on the part of
someone not so familiar with working with names as you & I are.<g>

 

[Dave Peterson]

On the other hand, it only requires a single change to a worksheet name -- which
makes it simpler <bg>.

 

[GS]

Dave Peterson formulated the question :

On the other hand, it only requires a single change to a worksheet name --
which makes it simpler <bg>.

True enough!<BG>

However, I don't think the OP was looking for a way to create defined
names for ranges via VBA. My understanding is he had a problem
referencing defined names he had already created. The context of my
replies has been to use local scope names and provide the correct
syntax to be used in the defined name dialog. I also gave some
suggestions for refering to these names from different sheets than they
were defined on. In all cases, the task involves manually entering the
name and so makes sense to do this in the defined name dialog at
worksheet design time.

I'd reserve using your suggestion for runtime usage where, for example,
I might be updating worksheets in an app workbook with new names. In
cases where a list of names need be updated in a multi-sheet workbook I
use something like Rob Bovey's table-driven approach to UI worksheet
management (Ch5 Professional Excel Development: Application Specific
Add-ins) for which names to be added to which sheets of the app
workbook. This is an awesome dev tool that, since implementing it, I
can't imagine developing anything without it.