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