Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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").... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
Dave Peterson explained :
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" 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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
On the other hand, it only requires a single change to a worksheet name -- which
makes it simpler <bg. On 01/21/2011 15:36, GS wrote: Dave Peterson explained : 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" 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name define on non active(focused) sheet
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need VBA Code to Let User Define Active Sheet | Excel Discussion (Misc queries) | |||
Closing the current focused sheet | Excel Discussion (Misc queries) | |||
Selecting Range in non-active sheet | Excel Programming | |||
Define Name in active cell | Excel Worksheet Functions | |||
define range name, use on VBA selected sheet | Excel Programming |