Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
difference of worksheet name and (name) property in VBA properties
In VBA within Excel, the worksheets have a name property and a (name)
property. One the user can change by right-clicking on the worksheet tab and change the name. This is also the same name field with which i know how to run a loop. My question is how to do i use the OTHER name field (the only that can only be changed within the VBA properties field) to run a loop? The purpose is to run a macro loop regardless of the names of the worksheet (i can't just lock off the worksheet names, they need to be left open to change) Any help is greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
difference of worksheet name and (name) property in VBA properties
Hi cydenney,
If you want to loop through all the worksheets regardless of name, you would do this: Dim ws As Worksheet For Each ws In Worksheets Debug.Print ws.Name '/ or other operations on ws object Next ws The (name) property is known as the CodeName for the worksheet. This comes in very handy if you want to leave the worksheet open to name changes - you can refer to the CodeName of the worksheet just like it's an object. I typically name my worksheets with the prefix "ws". So say I apply a CodeName of wsData to a worksheet. The user can rename the worksheet to whatever they want, and it won't affect this CodeName. So I can refer to that object like this: MsgBox wsData.Name '/ gives me current (UI) name of worksheet -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] wrote: In VBA within Excel, the worksheets have a name property and a (name) property. One the user can change by right-clicking on the worksheet tab and change the name. This is also the same name field with which i know how to run a loop. My question is how to do i use the OTHER name field (the only that can only be changed within the VBA properties field) to run a loop? The purpose is to run a macro loop regardless of the names of the worksheet (i can't just lock off the worksheet names, they need to be left open to change) Any help is greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
difference of worksheet name and (name) property in VBA proper
To elaborate on Jake's expanation (which is absolutly correct) the (Name)
refers to the sheet object, where as Name refers to the tab name which is a property of the object. So if you have not changed the code names of your sheets (which I agree with jake you should definitly do) then you can type a statement like msgbox sheet1.name Note the intellisense will work when you type this as opposed to msgbox worksheets("Sheet1").name This is because Sheet1 refers directly to the worksheet object, where as worksheets("Sheet1") referes to a sheet with the tab name Sheet1 which could turn out to be a chart. This will not be determined until run time so intellisense will not function as the things a worksheet can do and the things a chart can do are very different... -- HTH... Jim Thomlinson "Jake Marx" wrote: Hi cydenney, If you want to loop through all the worksheets regardless of name, you would do this: Dim ws As Worksheet For Each ws In Worksheets Debug.Print ws.Name '/ or other operations on ws object Next ws The (name) property is known as the CodeName for the worksheet. This comes in very handy if you want to leave the worksheet open to name changes - you can refer to the CodeName of the worksheet just like it's an object. I typically name my worksheets with the prefix "ws". So say I apply a CodeName of wsData to a worksheet. The user can rename the worksheet to whatever they want, and it won't affect this CodeName. So I can refer to that object like this: MsgBox wsData.Name '/ gives me current (UI) name of worksheet -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] wrote: In VBA within Excel, the worksheets have a name property and a (name) property. One the user can change by right-clicking on the worksheet tab and change the name. This is also the same name field with which i know how to run a loop. My question is how to do i use the OTHER name field (the only that can only be changed within the VBA properties field) to run a loop? The purpose is to run a macro loop regardless of the names of the worksheet (i can't just lock off the worksheet names, they need to be left open to change) Any help is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare excel worksheet and output difference to new worksheet | Excel Discussion (Misc queries) | |||
Worksheet Custom Properties | Excel Programming | |||
Print>Properties>Layout>Pages per sheet: this function/property . | Excel Discussion (Misc queries) | |||
Worksheet properties | Excel Programming | |||
worksheet Name and (Name) properties | Excel Programming |