How do l get the sheet name from the sheet code name?

[michael.beckinsale]

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

 

[Peter T]

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

 

[Don Guillett]

Option Explicit
Sub sheetnames()
Dim i As Long
For i = 1 To Sheets.Count
'MsgBox Sheets(i).Name
sheets("mymodules").Cells(i, 1) = Sheets(i).Name
Next i
End Sub

 

[michael.beckinsale]

Hi Peter / Don

Many thanks for your replies, hope you can help out with a bit of
tweaking!

Peter - The code below is what l have ended up using along the lines
of your suggestion. I would not have thought about approaching it this
way at all, l simply thought that you could reference a cell that
contained a sheet codename and return the sheet name!

Sub GetSheetCodeName()

Dim sCodename As String

For i = 1 To 26
sCodename = Cells(i, 1).Value
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = sCodename Then
Cells(i, 3).Value = ws.Name
Exit For
End If
Next
Next i


End Sub

I completely agree with your comment that this would be better done
using code within VBE. The following is the the code that Chip
supplied and VBComp.Name returns the sheetcode name. What code would
you use to return the sheet name as it appears on the sheet tab? I
have tried many combinations to no avail.

Sub ListModules()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim ws As Worksheet
Dim Rng As Range

Set VBProj = ActiveWorkbook.VBProject
Set ws = ActiveWorkbook.Worksheets(ModuleListSheet)
Set Rng = ws.Range("A1")

For Each VBComp In VBProj.VBComponents
Rng(1, 1).Value = VBComp.Name
Rng(1, 2).Value = ComponentTypeToString(VBComp.Type)
On Error Resume Next
Rng(1, 3).Value = ActiveWorkbook.Sheets(VBComp.Name).Name
On Error GoTo 0
Set Rng = Rng(2, 1)
Next VBComp

End Sub

Don - I am l missing something here (or most probably l didn't explain
myself clearly) but doesn't your code simply list the sheet names
starting in cell(1,1) ?

Kind regards

Michael

 

[Peter T]

You are agreeing with the opposite of what I proposed as being the best way!
IOW better to loop the sheets rather than bothering with the VBProject
object model

If you really want to return the sheet name that way you could do

sName = Activeworkbook.VBProject.VBComponents(sCode).Properties("Name")

Regards,
Peter T

 

[R Van Valkenburgh]

The following is a VBA function I have written to do just that:

Public Function ToSheetName(CodeName As String, quote As Integer) As String
'
' Will return the actual sheet name, given the original name (codename).
' There is also the option to have it quoted, or not, as some of the uses of
' the name may or may not expect it to be quoted.
'
Dim theSheets As Sheets
Dim aSheet As Variant
Set theSheets = Sheets
For Each aSheet In theSheets
If aSheet.CodeName = CodeName Then
If quote Then
ToSheetName = "'" & aSheet.name & "'"
Else
ToSheetName = aSheet.name
End If
Exit For
End If
Next aSheet
End Function



michael.beckinsale wrote:

How do l get the sheet name from the sheet code name?
30-Mar-10

I am sure this should be pretty simple but so far the answer ha
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 gon
fine. However l am having trouble retrieving the worksheet name tha
appears on the worksheet tabs. So using Chip's code l can retrieve th
sheet code names, Sheet1, Sheet2 etc and list them as required. Let
say the the tab name that appears to the users is "A" for Sheet1 an
"B" for Sheet

So lets say that l now have Sheet1 in Cell(1,1) of a sheet calle
"MyModules". The question is how do l get the tab name ("A") as i
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 ReturnTabNam

Cells(1,1).Nam
o
Sheets(Cells(1,1).Value).Nam
o
Sheets("MyModules").cells(1,1).Nam
et

End Su

nothing is returned

All help gratefully appreciate

Regard

Michael

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Repeating Structures Table Looping and Table Extract
http://www.eggheadcafe.com/tutorial...0-a5704fe31a76/biztalk-repeating-structu.aspx

 

[michael.beckinsale]

Hi All,

Many thanks for both solutions.

Peter sorry for the mistake. What l really meant was that l agree with
you that your code avoided having to access the vba project and
normally this would be a better approach. However since the project l
am working on does require access to the vba project and most of that
is already coded IMO it would be better in this instance to obtain the
sheet name form within that code. Hope this clears up the
misunderstanding and in future l will review my posts to ensure l
write what l mean!

I find this newsgroup an invaluable source of information and help and
l did not intend any disrespect.

Kind regards

Michael