I don't think I follow what you are really trying to do,

sRefersto = Cells(1,1).Name
That returns the Refersto property (ie the default property) of the name
applied to A1, if there is one. If there's no name it will error. If you
want to return the actual name

sName = Cells(1,1).Name.Name
this returns the Name's name

That's simple enough but I wonder if that's what you are really after, the
cell's contents are irrelevant as far as the Name is concerned.

Guessing, and as you mention you have been looking at the VBE, if you are
trying to set a reference to a sheet only knowing its codename you can do
that via the VBProject. However I think better (no need to worry about
Trust access to VBProject) to loop all sheets until you find it, eg

sCodename = "sht1"

for each ws in activeworkbook.worksheets
if ws.codename = sCodename then
sHtName = ws.Name
exit for
next

or simply work with ws in the If check, or retain the ws reference for
future use

Regards,
Peter T



"michael.beckinsale" wrote in message
...
I am sure this should be pretty simple but so far the answer has
eluded me.

I am doing a project which involves retrieving data about the vbe.
Thanks to Chip Pearson's excellent code examples so far it has gone
fine. However l am having trouble retrieving the worksheet name that
appears on the worksheet tabs. So using Chip's code l can retrieve the
sheet code names, Sheet1, Sheet2 etc and list them as required. Lets
say the the tab name that appears to the users is "A" for Sheet1 and
"B" for Sheet2

So lets say that l now have Sheet1 in Cell(1,1) of a sheet called
"MyModules". The question is how do l get the tab name ("A") as it
appears to the users?

Debug.Print Sheet1.Name returns "A"
Debug.Print Sheets(Sheet1.Name).Name returns "A"

but if you use code something along the lines of:

Sub ReturnTabName

Cells(1,1).Name
or
Sheets(Cells(1,1).Value).Name
or
Sheets("MyModules").cells(1,1).Name
etc

End Sub

nothing is returned!

All help gratefully appreciated

Regards

Michael