Activate sheets by codename from the Personal.xlsb

[Rowland Hamilton]

How do I activate/access sheets by codename from the Personal.xlsb
I want it to work for 2 scenarios:
1) Recently downloaded Excel report in the temporary folder, with one sheet, Worksheet name and codename are both sheet1.

2) Workbook has been saved and the worksheet name has been changed from Sheet1 but the codename is still Sheet1 and I want to refer to the codename.

 

[Rowland Hamilton]

Your code behaves the same way. It works from the PERSONAL.XLSB if I run it from the VBE at least once. But if I save it there, close Excel. Open Excel again,
go to Developer>Macros>PERSONAL.XLSB!Test>Run, it gives an erroneous result. It returns the message "Not Found", but, of course, it should have been found because I opened a fresh workbook that definitely had a sheet codenamed "Sheet1". If step into your code and run it again, the message will read "Sheet1". The contradictory results can't be correct with the same inputs.
I mispoke earlier, when I said your code "breaks" and that I could hit "end" the debugger. But the result is wrong for the same reason.

 

[Rowland Hamilton]

So, how do I fix this?

 

[shg]

It works fine for me under all circumstances.

When I first open Excel and run the macro from a quick launch button ...

You mean a button you added to the Quick Access Toolbar?

 

[Rowland Hamilton]

Have you been saving the code, closing Excel, reopening Excel, then running the macro without going into the VB Editor window? Try this code on your generic Excel Book1. If you run it one time from the VB Editor window, it will work that time and any time after, no matter how you run it. But, if you don't run it from the VB Editor window at least once, and launch it from the quick access toolbar or the developer tab>Macros>choose the macro>Run, it won't work.

Sub testnine()
Dim strWScode As String
ActiveWorkbook.Activate

strWScode = ActiveSheet.CodeName
MsgBox strWScode
End Sub

 

[shg]

I added it to Personal, saved Personal, closed and reopened Excel, and ran it from the Macros dialog.

 

[Rowland Hamilton]

shg, I appreciate your help on this. You've gone above and beyond. My Excel settings must be different. Maybe I need to add something. Like, in Available References, I only have Visual Basic For Applications, Microsoft Excel 12.0 Object Library, OLE Automation and Microsoft Office 12.0 Object Library.

 

[RoryA]

What happens if you amend the function code to:

Function GetWorksheetFromCodeName(wkb As Workbook, sCodeName As String) As Worksheet
    Dim wks         As Excel.Worksheet
    Debug.print wkb.name
    For Each wks In wkb.Worksheets
        debug.print wks.codename
        If wks.CodeName = sCodeName Then
            Set GetWorksheetFromCodeName = wks
            Exit Function
        End If
    Next wks
End Function

If you still see the message that it's not found, what is in the immediate window after you open the VB Editor?

 

[shg]

Thanks for jumping in, Rory.