Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do l get the sheet name from the sheet code name?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do l get the sheet name from the sheet code name?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do l get the sheet name from the sheet code name?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do l get the sheet name from the sheet code name?
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).Prope rties("Name") Regards, Peter T "michael.beckinsale" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Sheet Name from the Code Name
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...g-structu.aspx |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Sheet Name from the Code Name
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help to code Macro to Copy fron one sheet and paste in other sheet | Excel Programming | |||
Copying code behind from a sheet to a sheet in another workbook. | Excel Programming | |||
Programmatically determining CODE NAME for sheet based upon Sheet | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming | |||
unprotect sheet in code and make sheet visible | Excel Programming |