Worksheet and codename handling

QuestionEdit

I have a worksheet with a worksheet CodeName=wksDatabase and worksheet name="Database". (shows up in the object browser as wksDatabase(Database). Since the user may change the worksheet name I am trying to program using the Codename. I use the following VBA to copy the Codename into a cell in another worksheet: cells(1,1).value=wksDatabase.codename which puts a string in the cell. In a later macro I need to access then worksheet name (Database) based on the codename stored in cells(1,1). I am using the statement: set ws=ThisWorkbook.VBProject.VBComponents(Cells(1, 1)) with ws Dim as an object but I can't seem to get the worksheet name. ws.name=wksDatabase. Is there code that will give the worksheet name? (I tried to Dim ws as a worksheet but then got a type mismatch error on the set statement.)

AnswerEdit

Steve,

I have a workbook with a sheet with a tab name of "AAA" and the codename is "Sheet1"

Here is a demo of how I can use these. I did this in the immediate window then copied it here

? thisworkbook.VBProject.vbcomponents("Sheet1").properties("Name").Value AAA ? thisworkbook.VBProject.VBComponents("Sheet1").Name Sheet1 sName = thisworkbook.VBProject.vbcomponents("Sheet1").properties("Name").Value set sh = worksheets(sName) ? sh.Name AAA ? sh.Codename Sheet1

So in the first line I am using the codename to get the Tabe name. later I set a variable ("sName") to hold results of that query

so now I have the Tab name in the variable sName sName = thisworkbook.VBProject.vbcomponents("Sheet1").properties("Name").Value

so I use that to get a reference to (or refer to) the Worksheet

Dim sh as Worksheet set sh = worksheets(sName)

So you were pretty close Dim ws as Worksheet sName = ThisWorkbook.VBProject.VBComponents(Cells(1, 1)).Properties("Name").value set ws = Worksheets(sName)

If that doesn't answer the question or you need more information, post back with the particulars.