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

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

On 21 Jan., 10:50, ell wrote:
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

On Jan 21, 4:29*am, Per Jessen wrote:
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

On 21 Jan., 10:50, ell wrote:



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- Hide quoted text -

- Show quoted text -

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

On Jan 21, 3:55*pm, Don Guillett Excel MVP
wrote:
On Jan 21, 4:29*am, Per Jessen wrote:









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

On 21 Jan., 10:50, ell wrote:

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- Hide quoted text -

- Show quoted text -

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...

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

--
Garry

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

On Jan 21, 9:14*pm, GS wrote:
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

--
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")....

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

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



On 01/21/2011 13:14, GS wrote:
Sheets("Sheet2").Range("A1").Name = "'Sheet2'!Don"

--
Dave Peterson