Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference sheet by number or name
In Excel2007;
In past threads I've read conflicting comments re. referencing sheets by number or code name. In VBA, how can I refer to the same sheet every time regardless of the sheets code name or position? Note the name or position may change often. Thanks. Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference sheet by number or name
Names, indexes and codenames can all be changed. Since codenames are least
likely to be changed by the user I'd use them. -- Jim "Bassman62" wrote in message ... | In Excel2007; | In past threads I've read conflicting comments re. referencing sheets by | number or code name. | In VBA, how can I refer to the same sheet every time regardless of the | sheets code name or position? | Note the name or position may change often. | Thanks. | | Dave | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference sheet by number or name
There are 3 ways to refer to a sheet; by index number, by tab name or by code
name. Index number is inherantly dangerous as the index of the sheet chan change based on things that your end user can do such as deleting existing sheets. Tab name is inherantly dangerous as your code will crash if the user changes the name of the sheet. Code name is darn near fool proof. There is nothing that the average and user can do to change the code name. Additionally the code name refers directly to the object so your code is shorter and easier to write. -- HTH... Jim Thomlinson "Bassman62" wrote: In Excel2007; In past threads I've read conflicting comments re. referencing sheets by number or code name. In VBA, how can I refer to the same sheet every time regardless of the sheets code name or position? Note the name or position may change often. Thanks. Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference sheet by number or name
Thank you.
I had mistaken 'Tab' for 'Code Name'. I see now that I can change the (Name) in the properties of the object without affecting the Tab identifier. I presume this to be the 'Code Name'. How then do I refer to the Code Name within the code? Dave "Jim Thomlinson" wrote: There are 3 ways to refer to a sheet; by index number, by tab name or by code name. Index number is inherantly dangerous as the index of the sheet chan change based on things that your end user can do such as deleting existing sheets. Tab name is inherantly dangerous as your code will crash if the user changes the name of the sheet. Code name is darn near fool proof. There is nothing that the average and user can do to change the code name. Additionally the code name refers directly to the object so your code is shorter and easier to write. -- HTH... Jim Thomlinson "Bassman62" wrote: In Excel2007; In past threads I've read conflicting comments re. referencing sheets by number or code name. In VBA, how can I refer to the same sheet every time regardless of the sheets code name or position? Note the name or position may change often. Thanks. Dave |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference sheet by number or name
Jim,
I think I've got it. I was trying to refer to it as a Worksheet or Sheet but it looks like I can simply refer to it as I would a variable. Thank you very much. Dave "Jim Thomlinson" wrote: There are 3 ways to refer to a sheet; by index number, by tab name or by code name. Index number is inherantly dangerous as the index of the sheet chan change based on things that your end user can do such as deleting existing sheets. Tab name is inherantly dangerous as your code will crash if the user changes the name of the sheet. Code name is darn near fool proof. There is nothing that the average and user can do to change the code name. Additionally the code name refers directly to the object so your code is shorter and easier to write. -- HTH... Jim Thomlinson "Bassman62" wrote: In Excel2007; In past threads I've read conflicting comments re. referencing sheets by number or code name. In VBA, how can I refer to the same sheet every time regardless of the sheets code name or position? Note the name or position may change often. Thanks. Dave |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference sheet by number or name
Note that refering to the sheet directly you get the intellisence drop down
as the object know its properties and methods. If you use the tab name then you could be refering to a worksheet or a chart sheet. That being the case you loose the intellisence as the nature of the sheet will not be know until run time. -- HTH... Jim Thomlinson "Bassman62" wrote: Jim, I think I've got it. I was trying to refer to it as a Worksheet or Sheet but it looks like I can simply refer to it as I would a variable. Thank you very much. Dave "Jim Thomlinson" wrote: There are 3 ways to refer to a sheet; by index number, by tab name or by code name. Index number is inherantly dangerous as the index of the sheet chan change based on things that your end user can do such as deleting existing sheets. Tab name is inherantly dangerous as your code will crash if the user changes the name of the sheet. Code name is darn near fool proof. There is nothing that the average and user can do to change the code name. Additionally the code name refers directly to the object so your code is shorter and easier to write. -- HTH... Jim Thomlinson "Bassman62" wrote: In Excel2007; In past threads I've read conflicting comments re. referencing sheets by number or code name. In VBA, how can I refer to the same sheet every time regardless of the sheets code name or position? Note the name or position may change often. Thanks. Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference sheet number not name | Excel Worksheet Functions | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Column letter reference as number reference | Excel Programming | |||
Formula to reference sheet number base on cell | Excel Programming |