Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
Hi experts in the community,
as VBA newbie I know there are two possibilities to reference a worksheet in an application: Example: ... Worksheets(workSheetID).Range("openIcon").Value ... where workSheetID is either: a.) a numerical ID, the sheet index or b.) a string, the name of the sheet as it is displayed in the sheet tabs. Question: how can I reference the sheet in VBA without being intertwined by either sheet name changes or insertions of new sheets by the user? Thanks for your help Have a nice day Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
The codename is much more difficult for the user to screw up.
Open the VBE Hit ctrl-r to see the project explorer Expand one of the projects Under the Microsoft Excel Objects, you'll see the sheets in your workbook. Sheet1 (Sheet1) Sheet2 (Sheet Name that User Sees Here) The name in ()'s is the name of the worksheet--the name that the user sees on the Tab. The name before the ()'s is the codename. If you select one of the sheets in the project explorer and hit F4, you'll see the properties window. The (Name) property is the codename. You can change the codename to something meaningful to make your code easier to read. Worksheets(1).range("a1").value = "hi" or worksheets("Sheet1").range("a1").value = "hi" can be replaced with the codename (say Prices) Prices.range("a1").value = "hi" "Michael.Tarnowski" wrote: Hi experts in the community, as VBA newbie I know there are two possibilities to reference a worksheet in an application: Example: ... Worksheets(workSheetID).Range("openIcon").Value ... where workSheetID is either: a.) a numerical ID, the sheet index or b.) a string, the name of the sheet as it is displayed in the sheet tabs. Question: how can I reference the sheet in VBA without being intertwined by either sheet name changes or insertions of new sheets by the user? Thanks for your help Have a nice day Michael -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
A few tiny trick. Say we have a worksheet that may change both its name and
position, but we want to be able to find it. Pick a cell on the sheet and assing a Defined Name to the cell, say "qwerty" Once this is done, you can move the sheet around and change its name, but: Sub dural() MsgBox (Range("qwerty").Parent.Name) End Sub will always give you its correct name and: Sheets(Range("qwerty").Parent.Name).Activate will get you there. As I said, a shabby little trick, but it works. -- Gary''s Student - gsnu2007K "Michael.Tarnowski" wrote: Hi experts in the community, as VBA newbie I know there are two possibilities to reference a worksheet in an application: Example: ... Worksheets(workSheetID).Range("openIcon").Value ... where workSheetID is either: a.) a numerical ID, the sheet index or b.) a string, the name of the sheet as it is displayed in the sheet tabs. Question: how can I reference the sheet in VBA without being intertwined by either sheet name changes or insertions of new sheets by the user? Thanks for your help Have a nice day Michael |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
On Feb 6, 12:24 am, Gary''s Student
wrote: A few tiny trick. Say we have a worksheet that may change both its name and position, but we want to be able to find it. Pick a cell on the sheet and assing a Defined Name to the cell, say "qwerty" Once this is done, you can move the sheet around and change its name, but: Sub dural() MsgBox (Range("qwerty").Parent.Name) End Sub will always give you its correct name and: Sheets(Range("qwerty").Parent.Name).Activate will get you there. As I said, a shabby little trick, but it works. -- Gary''s Student - gsnu2007K "Michael.Tarnowski" wrote: Hi experts in the community, as VBA newbie I know there are two possibilities to reference a worksheet in an application: Example: ... Worksheets(workSheetID).Range("openIcon").Value ... where workSheetID is either: a.) a numerical ID, the sheet index or b.) a string, the name of the sheet as it is displayed in the sheet tabs. Question: how can I reference the sheet in VBA without being intertwined by either sheet name changes or insertions of new sheets by the user? Thanks for your help Have a nice day Michael Gary, Dave -- nice suggestions, I will work with that. But, how should I proceed if I have define global variables for different worksheets? -- e.g. const wsShID_1 = "Config" const wsShID_2 = "Sheet 1" and so one... ? Michael |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
If this was meant for me, I don't understand the question.
"Michael.Tarnowski" wrote: <<snipped Gary, Dave -- nice suggestions, I will work with that. But, how should I proceed if I have define global variables for different worksheets? -- e.g. const wsShID_1 = "Config" const wsShID_2 = "Sheet 1" and so one... ? Michael -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
On Feb 6, 1:25 am, Dave Peterson wrote:
If this was meant for me, I don't understand the question. "Michael.Tarnowski" wrote: <<snipped Gary, Dave -- nice suggestions, I will work with that. But, how should I proceed if I have define global variables for different worksheets? -- e.g. const wsShID_1 = "Config" const wsShID_2 = "Sheet 1" and so one... ? Michael -- Dave Peterson To make my point clear: If I want to address the worksheets by global constants, I would use the (user)names of the sheets / or the sheet index, like const worksheetID = 1 or const worksheetID = "MySheet" Is there a function in VBA to determine the values (sheet names or indices) for the constants, like const worksheetID = foo(...) Michael |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
On Feb 6, 1:25 am, Dave Peterson wrote:
If this was meant for me, I don't understand the question. "Michael.Tarnowski" wrote: <<snipped Gary, Dave -- nice suggestions, I will work with that. But, how should I proceed if I have define global variables for different worksheets? -- e.g. const wsShID_1 = "Config" const wsShID_2 = "Sheet 1" and so one... ? Michael -- Dave Peterson To make my point clear: If I want to address the worksheets by global constants, I would use the (user)names of the sheets / or the sheet index, like const worksheetID = 1 or const worksheetID = "MySheet" Is there a function in VBA to determine the values (sheet names or indices) for the constants, like const worksheetID = foo(...) Michael |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
You could add a worksheet level name (hidden) to each sheet and then use that.
But it makes much more sense to me to use the codename. "Michael.Tarnowski" wrote: On Feb 6, 1:25 am, Dave Peterson wrote: If this was meant for me, I don't understand the question. "Michael.Tarnowski" wrote: <<snipped Gary, Dave -- nice suggestions, I will work with that. But, how should I proceed if I have define global variables for different worksheets? -- e.g. const wsShID_1 = "Config" const wsShID_2 = "Sheet 1" and so one... ? Michael -- Dave Peterson To make my point clear: If I want to address the worksheets by global constants, I would use the (user)names of the sheets / or the sheet index, like const worksheetID = 1 or const worksheetID = "MySheet" Is there a function in VBA to determine the values (sheet names or indices) for the constants, like const worksheetID = foo(...) Michael -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference Worksheets in VBA properly
On Feb 10, 2:55 pm, Dave Peterson wrote:
You could add a worksheet level name (hidden) to each sheet and then use that. But it makes much more sense to me to use the codename. "Michael.Tarnowski" wrote: On Feb 6, 1:25 am, Dave Peterson wrote: If this was meant for me, I don't understand the question. "Michael.Tarnowski" wrote: <<snipped Gary, Dave -- nice suggestions, I will work with that. But, how should I proceed if I have define global variables for different worksheets? -- e.g. const wsShID_1 = "Config" const wsShID_2 = "Sheet 1" and so one... ? Michael -- Dave Peterson To make my point clear: If I want to address the worksheets by global constants, I would use the (user)names of the sheets / or the sheet index, like const worksheetID = 1 or const worksheetID = "MySheet" Is there a function in VBA to determine the values (sheet names or indices) for the constants, like const worksheetID = foo(...) Michael -- Dave Peterson Thank you Dave for sharing your insights. Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cleaning up Excel 97-2003 worksheets to work properly in Office 20 | Excel Discussion (Misc queries) | |||
Cleanup worksheets from 97-2003 to work properly on Excel 2007 | Excel Worksheet Functions | |||
Reference different worksheets | Excel Discussion (Misc queries) | |||
3d reference not working properly | Excel Worksheet Functions | |||
Worksheets Don't Properly Send When Emailing | Excel Discussion (Misc queries) |