Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing sheet name changes (or a workaround)
We are creating a workbook that will need to activate several different
sheets and perform some calculations. Is there a way to address the following: Sheet name changed by user - macro looking for sheet name on tab Sheet reordered in workbook It seems as though I saw a way to define a "hidden" name for the worksheet that the users couldn't see, but I can't seem to find it. Does this exist? If so, how would I use it? Thanks, Barb Reinhardt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing sheet name changes (or a workaround)
go into the VBE and look at the project window. You will see each sheet has
two names. One is the tab name, the other is the "codename" sheet1.Range("A1").Value = 1 uses the code name. Although this can be changed, it is significanly less likely that a user would do this. -- Regards, Tom Ogilvy "Barb Reinhardt" wrote: We are creating a workbook that will need to activate several different sheets and perform some calculations. Is there a way to address the following: Sheet name changed by user - macro looking for sheet name on tab Sheet reordered in workbook It seems as though I saw a way to define a "hidden" name for the worksheet that the users couldn't see, but I can't seem to find it. Does this exist? If so, how would I use it? Thanks, Barb Reinhardt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing sheet name changes (or a workaround)
Barb,
You are looking fir the .Codename of the WS. The user cannot change it. It works like an object, rather than a name: WS_WithCodeName.Range("A1").Value=NewValue NickHK "Barb Reinhardt" ... We are creating a workbook that will need to activate several different sheets and perform some calculations. Is there a way to address the following: Sheet name changed by user - macro looking for sheet name on tab Sheet reordered in workbook It seems as though I saw a way to define a "hidden" name for the worksheet that the users couldn't see, but I can't seem to find it. Does this exist? If so, how would I use it? Thanks, Barb Reinhardt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing sheet name changes (or a workaround)
What you want to do is to use the sheet codename, not the sheet tab name. In
the VBE Properties for each sheet there is a (Name) and a Name. The first one (Name) refers to the code name and has nothing to do with the tab name. In the VBE Project Exploere wyou will see all of the sheets listed something like this... Sheet1(Tab This) Sheet2(Tab That) .... You can change the code name of the sheet by changing the Value of (Name) in properties. I would recommend you do this as it makes your code much easier to read. Do so for each sheet in the book. It takes a minute but it saves time, effort and confusion down the road. Note the project explorer will look like this shtThis(Tab This) shtThat(Tab That) .... In code you can refer dircetly to the sheet by referencing the code name msgbox shtThis.Name One nice advantage to doing this is that intellisence will work with shtThis, where as it will not work with Worksheets("Tab This")... -- HTH... Jim Thomlinson "Barb Reinhardt" wrote: We are creating a workbook that will need to activate several different sheets and perform some calculations. Is there a way to address the following: Sheet name changed by user - macro looking for sheet name on tab Sheet reordered in workbook It seems as though I saw a way to define a "hidden" name for the worksheet that the users couldn't see, but I can't seem to find it. Does this exist? If so, how would I use it? Thanks, Barb Reinhardt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing sheet name changes (or a workaround)
Thanks everyone for your assistance. That really helps clean up my code.
"NickHK" wrote: Barb, You are looking fir the .Codename of the WS. The user cannot change it. It works like an object, rather than a name: WS_WithCodeName.Range("A1").Value=NewValue NickHK "Barb Reinhardt" ... We are creating a workbook that will need to activate several different sheets and perform some calculations. Is there a way to address the following: Sheet name changed by user - macro looking for sheet name on tab Sheet reordered in workbook It seems as though I saw a way to define a "hidden" name for the worksheet that the users couldn't see, but I can't seem to find it. Does this exist? If so, how would I use it? Thanks, Barb Reinhardt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing sheet name changes (or a workaround)
How would I use the code name in a formula.
I have something like this now: Cells(i, "E").FormulaR1C1 Let's say the Sheet code Name is BarbsTest "Tom Ogilvy" wrote: go into the VBE and look at the project window. You will see each sheet has two names. One is the tab name, the other is the "codename" sheet1.Range("A1").Value = 1 uses the code name. Although this can be changed, it is significanly less likely that a user would do this. -- Regards, Tom Ogilvy "Barb Reinhardt" wrote: We are creating a workbook that will need to activate several different sheets and perform some calculations. Is there a way to address the following: Sheet name changed by user - macro looking for sheet name on tab Sheet reordered in workbook It seems as though I saw a way to define a "hidden" name for the worksheet that the users couldn't see, but I can't seem to find it. Does this exist? If so, how would I use it? Thanks, Barb Reinhardt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing sheet name changes (or a workaround)
AND ... how do I reference the sheet code names in formulas.
"Tom Ogilvy" wrote: go into the VBE and look at the project window. You will see each sheet has two names. One is the tab name, the other is the "codename" sheet1.Range("A1").Value = 1 uses the code name. Although this can be changed, it is significanly less likely that a user would do this. -- Regards, Tom Ogilvy "Barb Reinhardt" wrote: We are creating a workbook that will need to activate several different sheets and perform some calculations. Is there a way to address the following: Sheet name changed by user - macro looking for sheet name on tab Sheet reordered in workbook It seems as though I saw a way to define a "hidden" name for the worksheet that the users couldn't see, but I can't seem to find it. Does this exist? If so, how would I use it? Thanks, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have a workaround | Excel Worksheet Functions | |||
Linked Spreadsheets - Preventing Access to Source Sheet | Excel Discussion (Misc queries) | |||
Preventing Right Click On Sheet Tab??? | Excel Programming | |||
Preventing users to move object in the sheet | Excel Programming | |||
Preventing someone from unhiding a sheet | Excel Programming |