Sheet Code Name vs. Sheet Name

[LisaKan]

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

 

[Jim Thomlinson]

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...

 

[Dave Peterson]

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"

 

[Bill Renaud]

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.

 

[LisaKan]

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.

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!