Prev: How Do I?
Next: Close msgbox after making selection
From: R Van on 30 Mar 2010 20:59
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 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

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/tutorials/aspnet/73bf7539-4c13-43a5-a580-a5704fe31a76/biztalk-repeating-structu.aspx
From: michael.beckinsale on 31 Mar 2010 03:35
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
 | 
Pages: 1
Prev: How Do I?
Next: Close msgbox after making selection