Get sheet name from composite code name

[Giordano Bruno]

Is there a simple way of retrieving the worksheet name from the code name when the code name = "Sheet" & "1", "2", "3", etc. I can do it by looping through all worksheets, but there must be an easier way.
Thanks in advance for any suggestions.

 

[Giordano Bruno]

Hi Jaafar. Thanks for the instructions. In fact I had tried that approach (among others) with no better results. After experimenting with the code, I discovered that it works only when the sheet name has no spaces. Most of the sheets I was importing had a space in the names and it was only these sheets that produced the error message. Can you think of a simple way of overcoming this limitation (other than removing all spaces from all imported worksheets).

 

[Trezore]

With ThisWorkbook
    For i = 1 To 9
        MsgBox .Sheets(.VBProject.VBComponents("Sheet" & i).Properties("index")).Name
    Next i
End With

You'll need to tick "Trust Access to the VBA project object model" in Excel's Trust Settings.

THANK YOU Stephen!!!
This is exactly what I needed... with slight change...

I needed to find the associated Sheetname to the VBComponent result for exporting modules, forms, sheets.
When it got to the Sheets export, all I would see is Sheet1.cls, Sheet12.cls and no clue what file was for what...
This whole piece here --> ("Sheet" & i).Properties("index") ).Name <-- was what I was struggling to find.
Didn't even dawn on me that it's .Sheets( # ).Name and # = all this cr@p --> .VBProject.VBComponents("Sheet" & i).Properties("index")
After VBComponents, I was trying .next attributes
Then also had to deal with detecting hidden sheets to skip exporting

For Each objVBComp in objMyProj.VBComponents
    If objVBComp.Type = vbtext_ct_StdModule Then
        objVBComp.Export "C:\filepath\" & objVBComp.Name & ".bas"
    Elseif objVBComp.Type = "100" Then
        comname = objVBComp.Name
        If comname = "ThisWorkbook" Then
            objVBComp.Export "C:\filepath\" & objVBComp.Name & ".cls"
        Else
            sheetnum = Right(comname, Len(comname)-5)
            [COLOR=rgb(147, 101, 184)]With ThisWorkbook[/COLOR]
               [COLOR=rgb(147, 101, 184)] [/COLOR][COLOR=rgb(41, 105, 176)]sheetname[/COLOR][COLOR=rgb(65, 168, 95)] =[/COLOR][COLOR=rgb(147, 101, 184)] .Sheets(.VBProject.VBComponents("Sheet" & [/COLOR][COLOR=rgb(41, 105, 176)]sheetnum[/COLOR][COLOR=rgb(147, 101, 184)]).Properties("index")).Name[/COLOR]
            [COLOR=rgb(147, 101, 184)]End With[/COLOR]
            If wkb is Nothing Then Set wkb = ActiveWorkbook
            [COLOR=rgb(65, 168, 95)]' For checking if sheet is visible, not hidden[/COLOR]
            If wkb.Sheets(sheetname).visible = xlSheetVisible Then
                objVBComp.Export "C:\filepath\" & objVBComp.Name & " (" & sheetname & ").cls"
            Else
               [COLOR=rgb(65, 168, 95)] 'skip, sheet is hidden[/COLOR]
            End If
        End If
    End If
Next

Now I just need to perform the reverse for importing .cls files and having them numbered correctly.

 

[Trezore]

With ThisWorkbook
    For i = 1 To 9
        MsgBox .Sheets(.VBProject.VBComponents("Sheet" & i).Properties("index")).Name
    Next i
End With

You'll need to tick "Trust Access to the VBA project object model" in Excel's Trust Settings.

THANK YOU Stephen!!!
This is exactly what I needed... with slight change...

I needed to find the associated Sheetname to the VBComponent result for exporting modules, forms, sheets.
When it got to the Sheets export, all I would see is Sheet1.cls, Sheet12.cls and no clue what file was for what...
This whole piece here --> ("Sheet" & i).Properties("index") ).Name <-- was what I was struggling to find.
Didn't even dawn on me that it's .Sheets( # ).Name and # = all this cr@p --> .VBProject.VBComponents("Sheet" & i).Properties("index")
After VBComponents, I was trying .next attributes
Then also had to deal with detecting hidden sheets to skip exporting

Cleaned up my post... sorry for duplicate, could not find the delete previous after seeing the crazy color codes inserted when selecting VBA and having added colors.

For Each objVBComp in objMyProj.VBComponents
    If objVBComp.Type = vbtext_ct_StdModule Then
        objVBComp.Export "C:\filepath\" & objVBComp.Name & ".bas"
    Elseif objVBComp.Type = "100" Then
        comname = objVBComp.Name
        If comname = "ThisWorkbook" Then
            objVBComp.Export "C:\filepath\" & objVBComp.Name & ".cls"
        Else
            sheetnum = Right(comname, Len(comname)-5)
            With ThisWorkbook
               sheetname = .Sheets(.VBProject.VBComponents("Sheet" & sheetnum).Properties("index")).Name
            End With
            If wkb is Nothing Then Set wkb = ActiveWorkbook
            ' For checking if sheet is visible, not hidden
            If wkb.Sheets(sheetname).visible = xlSheetVisible Then
                objVBComp.Export "C:\filepath\" & objVBComp.Name & " (" & sheetname & ").cls"
            Else
               'skip, sheet is hidden
            End If
        End If
    End If
Next

Now I just need to perform the reverse for importing .cls files and having them numbered correctly.

 

[Peter_SSs]

the crazy color codes inserted when selecting VBA and having added colors.

To add colour like that, use the RICH code tags, not the VBA code tags