Excel - how do use VBA to select by object name instead of sheet name? 1

[y2k1981]

I guess the subject line pretty much says it all!! I'm in the process or writing some macros for somebody. how do I select an worksheet by referencing the object name instead of the sheetname. For example sheet1 is called "Phone", so I have

with Worksheets("Phone")

But I want to put Sheet1 in the brackets instead, as the user may want to change the sheet name from something other than phone. I've tried

with Worksheets(Sheet1)

- ie leaving out the " & " and I've tried

with Object(Sheet1)

and

with Objects(Sheet1)

but to no avail. how do I do this?

 

[dsi]

You can try using the CodeName property.
Here is a little code that lists all of the sheet names and associated code names:

Sub ListSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        MsgBox ws.Name & vbCrLf & ws.CodeName
   Next ws
End Sub

 

[xlbo]

If you want to use the order of the sheets in the workbook then

With sheets(1)

will work with the 1st sheets (ie left most sheet)
etc etc Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[y2k1981]

dsi - I don't understand, how can I replace my with Worksheets("Sheetname&quot with your code?

xlbo - your suggestion is good, but as I won't be the user, I'm afraid that the users may move the sheets about. Isn't there any other alternative?

 

[combo]

Both worksheet and chart pages have (Name) property, which can be changed by the user in excel window, and Name property, which can be changed only either via Property window or using code as CodeName property.
To refer to the sheet use simply it's codename:
[tt]Sheet1.[/tt]
and select rest from drop-down list.

 

[y2k1981]

perfect ... thanks combo
and thanks everybody for responding