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.

 

[StephenCrump]

Hi Stephen. That's more or less what I am doing except that there are nine worksheets being imported. I had hoped to find a simple one line solution, but it does not appear to be possible.

So you're going to be looping from 1 to 9 in any event to process each worksheet. If you know where the worksheets will be located, e.g always starting at worksheet number 3, and consecutive, you can loop from Worksheets(3) to Worksheets(11).

If they could be anywhere, you'll need to rely on an indirect method, e.g. testing whether worksheet names are different to your pre-existing names, or codenames different to your pre-existing codenames. Whichever way you go will just add a few quick iterations to the loop.

 

[mikerickson]

Hi Mike. Nice to hear from you. I had thought of doing that, but it does require the user to put the new worksheets at the end. I have absolutely no confidence that the user will always do that so the Index property will not be reliable. I'm trying to make it foolproof even though I know that the fools will always outwit me in the end.

Perhaps you should have a companion "Put in the right order" macro that you run as needed. A utility that takes your input.

Is there anything in the structure of the sheets (number of columns?, column headers?) that would identify a sheet by it's contents?

 

[StephenCrump]

I'm trying to make it foolproof even though I know that the fools will always outwit me in the end.

This can be proven mathematically: Given a workbook protected N ways against dumb user behaviour, there exist at least N+1 ways the user will find to break it.

 

[Giordano Bruno]

Hi Stephen and Mike. I was looking for a one line solution. If that's not available, I might as well stick to a simple loop through all sheets. It's easier to write and does not really take so long that it's noticeable.
I like you mathematical proof Stephen. I think it's called "Murphy's Law".

 

[mikerickson]

One thing that I'm thinking is that if your loop only sets variables, then you will have to run that loop every time that VBA loses its values. But if you loop and re-position or re-tab-name the sheets, then you will have a clean workbook that can be processed in the future without looping.

The advantages of that vary depending on if you are doing a one-off type macro or if you are archiving stuff, but if you are archiving, it would be worth your wile to standardize every workbook that you get so that repeated sheet looping isn't needed.

 

[Giordano Bruno]

Hi Mike,
Always nice to hear from you. In this case, the code will be run only occasionally when a fresh load of data is available. Once the other worksheets are loaded, they are copied to the correct locations and the field headers are checked for consistency and then the new worksheets are deleted.
Many thanks for your contributions both here and in the past.

 

[Jaafar Tribak]

If the names of the imported worksheets are not edited by the user and stay the same throughout then would this work for you ?

Code in the ThisWorkbook Module :

Private WithEvents wb As Workbook

Private Sub Workbook_Open()
    Set wb = Me
End Sub

Private Sub wb_NewSheet(ByVal sh As Object)

    On Error Resume Next
        Me.CustomDocumentProperties(sh.CodeName).Delete
    On Error GoTo 0
    Me.CustomDocumentProperties.Add Name:=sh.CodeName, _
    LinkToContent:=False, _
    Type:=msoPropertyTypeString, _
    Value:=sh.Name

End Sub

Private Function GetSheetNameFromCodeName(ByVal CodeName As String) As String
    GetSheetNameFromCodeName = Me.CustomDocumentProperties(CodeName)
End Function

Sub test()
    MsgBox GetSheetNameFromCodeName(CodeName:="Sheet1")
End Sub

 

[Jaafar Tribak]

Using the _SheetActivate event instead of the _NewSheet would be better as follows :

Option Explicit

Private WithEvents wb As Workbook

Private Sub wb_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
        Me.CustomDocumentProperties(Sh.CodeName).Delete
    On Error GoTo 0
    Me.CustomDocumentProperties.Add Name:=Sh.CodeName, _
    LinkToContent:=False, _
    Type:=msoPropertyTypeString, _
    Value:=Sh.Name
End Sub

Private Sub Workbook_Open()
    Set wb = Me
End Sub

Private Function GetSheetNameFromCodeName(ByVal CodeName As String) As String
    GetSheetNameFromCodeName = Me.CustomDocumentProperties(CodeName)
End Function

Sub test()
    MsgBox GetSheetNameFromCodeName(CodeName:="Sheet1")
End Sub

 

[Giordano Bruno]

Hi Jaafar. Looks like black magic to me so I can't work out why when I run the "test" sub I get a Run-time error 5 on the "GetSheetNameFromCodeName = ..." line. All of the code is in the "ThisWorkbook" module and my workbook has worksheets Sheet1 thru Sheet9. Do you have any idea what I might be doing wrong?

 

[Jaafar Tribak]

Hi Jaafar. Looks like black magic to me so I can't work out why when I run the "test" sub I get a Run-time error 5 on the "GetSheetNameFromCodeName = ..." line. All of the code is in the "ThisWorkbook" module and my workbook has worksheets Sheet1 thru Sheet9. Do you have any idea what I might be doing wrong?

The code is meant to take effect when the workbook is re-opened (ie: the Workbook_Open event has to first be executed) and before the worksheets are imported.


Edit:
In other words:

1- run the Workbook_Open event
2- Import the worksheets
3- run the Test Macro