Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Code Name vs. Sheet Name
I have done alot of searching, and have found similar questions, but
everyone else seems to need the opposite of what I need. Here goes: I have mulitple users who have multiple copies of a paraticular workbook. Over the last several months, new sheets have been added to the workbook (by another programmer). Somehow, the Code Name to the sheets has changed. I'm not sure how this happened, since I didn't think that Excel changed the Sheet01 type of name. Maybe the other programmer 'tore apart' the workbook and 'put it back together again'. Maybe he was merging sheets from another workbook. No clue. So, what used to be: Sheet01(AAA) Sheet02(BBB) Is now: Sheet01(CCC) Sheet02(DDD) Sheet03(AAA) Sheet04(BBB) Anyway, here is my dilema. I need to create a 'fixer' type of workbook to place new code into the code modules of a couple of the existing workbooks sheets (AAA and BBB). I found code to do the following: Set oldCode = problemProject.VBComponents("Sheet01").CodeModule The problem is this. The workbook that is receiving the 'code fix' could be an older version, or the more recent version, so I cannot rely on the CodeName(Sheet01) being the correct sheet that I need. I need to find out how to refer to the Name that I gave it on the tab (AAA). I know that the name I gave it has not changed. Thanks for any help! Lisa |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Code Name vs. Sheet Name
To refer to a sheet by code name use
msgbox Sheet1.name to refer by tab name use msgbox Sheets("MyTabName").Name End users have the abiblity to change tab names. Only programmers have the ablitiyt to modify CodeNames... -- HTH... Jim Thomlinson "LisaKan" wrote: I have done alot of searching, and have found similar questions, but everyone else seems to need the opposite of what I need. Here goes: I have mulitple users who have multiple copies of a paraticular workbook. Over the last several months, new sheets have been added to the workbook (by another programmer). Somehow, the Code Name to the sheets has changed. I'm not sure how this happened, since I didn't think that Excel changed the Sheet01 type of name. Maybe the other programmer 'tore apart' the workbook and 'put it back together again'. Maybe he was merging sheets from another workbook. No clue. So, what used to be: Sheet01(AAA) Sheet02(BBB) Is now: Sheet01(CCC) Sheet02(DDD) Sheet03(AAA) Sheet04(BBB) Anyway, here is my dilema. I need to create a 'fixer' type of workbook to place new code into the code modules of a couple of the existing workbooks sheets (AAA and BBB). I found code to do the following: Set oldCode = problemProject.VBComponents("Sheet01").CodeModule The problem is this. The workbook that is receiving the 'code fix' could be an older version, or the more recent version, so I cannot rely on the CodeName(Sheet01) being the correct sheet that I need. I need to find out how to refer to the Name that I gave it on the tab (AAA). I know that the name I gave it has not changed. Thanks for any help! Lisa |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Code Name vs. Sheet Name
dim wks as worksheet
set wks = thisworkbook.worksheets("aaa") ThisWorkbook.VBProject.VBComponents(wks.codename). Name = "NewCodeName" 'or ThisWorkbook.VBProject.VBComponents(wks.CodeName) _ .Properties("_CodeName").Value = "NewCodeName2" LisaKan wrote: I have done alot of searching, and have found similar questions, but everyone else seems to need the opposite of what I need. Here goes: I have mulitple users who have multiple copies of a paraticular workbook. Over the last several months, new sheets have been added to the workbook (by another programmer). Somehow, the Code Name to the sheets has changed. I'm not sure how this happened, since I didn't think that Excel changed the Sheet01 type of name. Maybe the other programmer 'tore apart' the workbook and 'put it back together again'. Maybe he was merging sheets from another workbook. No clue. So, what used to be: Sheet01(AAA) Sheet02(BBB) Is now: Sheet01(CCC) Sheet02(DDD) Sheet03(AAA) Sheet04(BBB) Anyway, here is my dilema. I need to create a 'fixer' type of workbook to place new code into the code modules of a couple of the existing workbooks sheets (AAA and BBB). I found code to do the following: Set oldCode = problemProject.VBComponents("Sheet01").CodeModule The problem is this. The workbook that is receiving the 'code fix' could be an older version, or the more recent version, so I cannot rely on the CodeName(Sheet01) being the correct sheet that I need. I need to find out how to refer to the Name that I gave it on the tab (AAA). I know that the name I gave it has not changed. Thanks for any help! Lisa -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Code Name vs. Sheet Name
My 2 cents worth:
In workbooks that the user may change by inserting new worksheets, removing old ones, moving data to newly inserted worksheets, I generally avoid using the Code Name for a worksheet. I use the tab name instead. I might rewrite your code as follows: Dim wsAAA as Worksheet Dim wsBBB as Worksheet With ActiveWorkbook Set wsAAA = .Worksheets("AAA") Set wsBBB = .Worksheets("BBB") End With 'Continue processing using wsAAA and wsBBB for your references. This allows the users to move worksheets around, destroy the Code Names, etc. all they want. Your code will still work. Just make sure that they don't change the tab name from AAA to AA1, for example. This is a lot easier for them to understand than trying to maintain the Code Names. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Code Name vs. Sheet Name
On Dec 20, 12:12 am, "Bill Renaud"
wrote: My 2 cents worth: In workbooks that the user may change by inserting new worksheets, removing old ones, moving data to newly inserted worksheets, I generally avoid using the Code Name for a worksheet. I use the tab name instead. I might rewrite your code as follows: Dim wsAAA as Worksheet Dim wsBBB as Worksheet With ActiveWorkbook Set wsAAA = .Worksheets("AAA") Set wsBBB = .Worksheets("BBB") End With 'Continue processing using wsAAA and wsBBB for your references. This allows the users to move worksheets around, destroy the Code Names, etc. all they want. Your code will still work. Just make sure that they don't change the tab name from AAA to AA1, for example. This is a lot easier for them to understand than trying to maintain the Code Names. -- Regards, Bill Renaud Thanks for the quick responses. Jim: Your response was telling me how to refer to the sheet name when within the Sheets context. I needed to know how to refer the the name I gave it in the context of VBcomponents. Dave: Your response appeared to show me how to 'change' the name. I don't want to change it, just need to to how to refer to the name I already gave it. (My apologies if I misinterpreted it.) Bill: Your reply was very helpful. That got me going in the right direction. I tweaked the code your supplied to the following: Dim sAAA as String Dim sBBB as String With ActiveWorkbook sAAA = .Worksheets("AAA").CodeName sBBB = .Worksheets("BBB").CodeName End With Set oldCode = problemProject.VBComponents(sAAA).CodeModule That did the trick for me! Thanks for everyones help! Much appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying code behind from a sheet to a sheet in another workbook. | Excel Programming | |||
Programmatically determining CODE NAME for sheet based upon Sheet | Excel Programming | |||
How do i copy a active sheet to a new sheet with code and everything | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming | |||
unprotect sheet in code and make sheet visible | Excel Programming |