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]

run time error 91, object variable or with block variable not set on wsD.Name = "Download"
watch on wsD.name shows the function did not retrieve the codename (perhaps it is being bypassed?).

 

[Rowland Hamilton]

Also works if I step back to function then step through it so its hard to debug, because it works then.
Here is a simpler code to test, same problem, function doesn't work on first launch from quick button on quick access menu. PERSONAL.XLSB, but will work when I end the debugger and run a second time:

Sub Test_Won()
Dim wsPivot As Excel.Worksheet
Set wsPivot = GetWorksheetFromCodeName("Sheet1")
MsgBox wsPivot.Name
End Sub

[B][FONT=Courier New][COLOR=#006699]Function[/COLOR][/FONT][/B] <CODE style="COLOR: #000">GetWorksheetFromCodeName(</CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">ByVal</CODE> <CODE style="COLOR: #000">CodeName </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">As</CODE> <CODE style="COLOR: #069; FONT-WEIGHT: bold">String</CODE><CODE style="COLOR: #000">) </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">As</CODE> <CODE style="COLOR: #000">Worksheet
</CODE><CODE style="COLOR: #008200">' Return the worksheet with the requested code </CODE><CODE style="COLOR: #000">name.
</CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">Dim</CODE> <CODE style="COLOR: #000">FocusSheet </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">As</CODE> <CODE style="COLOR: #069; FONT-WEIGHT: bold">Object
</CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">  For</CODE> <CODE style="COLOR: #069; FONT-WEIGHT: bold">Each</CODE> <CODE style="COLOR: #000">FocusSheet </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">In</CODE> <CODE style="COLOR: #000">ActiveWorkbook.Worksheets
</CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">    If</CODE> <CODE style="COLOR: #000">FocusSheet.CodeName = CodeName  </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">Then
      </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">Set</CODE> <CODE style="COLOR: #000">GetWorksheetFromCodeName = </CODE><CODE style="COLOR: #000">FocusSheet
      </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">Exit</CODE> <CODE style="COLOR: #069; FONT-WEIGHT: bold">Function</CODE><CODE style="COLOR: #000"> 
    </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">End </CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">If
</CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">  Next</CODE> <CODE style="COLOR: #000">FocusSheet
</CODE><CODE style="COLOR: #069; FONT-WEIGHT: bold">End</CODE>

 

[shg]

Maybe ...

Sub Test()
    Dim wsPivot     As Excel.Worksheet

    Set wsPivot = GetWorksheetFromCodeName(ActiveWorkbook, "Sheet1")
    If wsPivot Is Nothing Then
        MsgBox "Not found"
    Else
        MsgBox wsPivot.Name
    End If
End Sub

Function GetWorksheetFromCodeName(wkb As Workbook, sCodeName As String) As Worksheet
    Dim wks         As Excel.Worksheet

    For Each wks In wkb.Worksheets
        If wks.CodeName = sCodeName Then
            Set GetWorksheetFromCodeName = wks
            Exit Function
        End If
    Next wks
End Function

 

[Rowland Hamilton]

Thanks, SHG. Same problem, When I first open Excel and run the macro from a quick launch button, it returns "Not Found" message, but, of course, it should have been found because I opened a fresh workbook that definitely had a sheet codenamed "Sheet1". If I stop the debugger and run the code you just gave me again (from quick launch button even), it does find th"Sheet1".

 

[Rowland Hamilton]

ALso will fail repeatedly if I don't stop the debugger or start initially from the VBE code window.

 

[Rowland Hamilton]

Doesn't work on home computer or work computer and the are on different networks. I must not have run it the same way before (starting from a fresh new instance of excel). Also found it won't work if launch the macro from a list (like in the developer tab). Only works after stopping the debugger or starting from line 1 of the code in the vbe and running it from there.

I found that the code will work to identify the sheet codenames in the PERSONAL.XLSB, but not to identify the codenames in the activeworkbook. Example:

Public Function GetWorksheetFromCodeName2( _
      ByVal CodeName As String) As Worksheet

' Return the worksheet with the requested code name.
   Dim FocusSheet As Object
   
   For Each FocusSheet In ActiveWorkbook.Worksheets
      If FocusSheet.CodeName = CodeName Then
         Set GetWorksheetFromCodeName2 = FocusSheet
         Exit Function
      End If
   Next FocusSheet

End Function

Sub Test_Dweeb()
Dim wsPivot As Excel.Worksheet
Workbooks("PERSONAL.XLSB").Activate
Set wsPivot = GetWorksheetFromCodeName2("Sheet1")
MsgBox wsPivot.Name
End Sub

 

[shg]

You can specify the workbook by name, Rowland. The code works fine for me.

 

[Rowland Hamilton]

I want it to work on any actual workbook I open but launch from the PERSONAL.XLSB. and, I want it to reference the codename of the sheet in the active workbook that is not the PERSONAL.XLSB.

 

[Rowland Hamilton]

Oddly enough, it will work, but only if I launch the code from the VB Editor window, or step into it, right before it decides to use the Codenames the way I want it to. To test the theory, I added debug.prints for my Immediate window:

Public Function GetWorksheetFromCodeName(ByVal CodeName As String) As Worksheet
' Return the worksheet with the requested code name.
   Dim FocusSheet As Object
   MsgBox "Running Test Code in " & ActiveWorkbook.Name
   Debug.Print "Activeworkbook name is: " & ActiveWorkbook.Name
   'Stop
   
   For Each FocusSheet In ActiveWorkbook.Worksheets
[B]   Debug.Print "First-FocusSheet.CodeName =" & FocusSheet.CodeName[/B]
   [B]Stop[/B]
      If FocusSheet.CodeName = CodeName Then
[B]      Debug.Print "Match-FocusSheet.CodeName =" & FocusSheet.CodeName
      Debug.Print "Match-CodeName =" & CodeName[/B]
      'Stop
         Set GetWorksheetFromCodeName = FocusSheet
         Debug.Print "Match-GetWorksheetFromCodeName = " & GetWorksheetFromCodeName.Name
         'Stop
         Exit Function
      End If
Debug.Print "Mismatch-FocusSheet.CodeName =" & FocusSheet.CodeName
Debug.Print "Mismatch-CodeName =" & CodeName
Stop
   Next FocusSheet
Debug.Print "Next FocusSheet.CodeName =" & FocusSheet.CodeName
Debug.Print "Next CodeName =" & CodeName
End Function
Sub Test_Won()
Dim wbREAL As Workbook
Dim wsREAL As Worksheet

Set wbREAL = ActiveWorkbook
'Workbooks("PERSONAL.XLSB").Activate
MsgBox "Running Test Code in " & wbREAL.Name
wbREAL.Activate
Set wsREAL = GetWorksheetFromCodeName("Sheet1")
MsgBox wsREAL.Name & " in " & wsREAL.Parent.Name
End Sub

The results are:

Activeworkbook name is: Book1
First-FocusSheet.CodeName =
Match-FocusSheet.CodeName =Sheet1
Match-CodeName =Sheet1
Match-GetWorksheetFromCodeName = Sheet1

When I open a fresh instance of excel and launch this code outside of the VBE, and Stop it at the bold stop, I I notice that the FocusSheet.CodeName is not populated before stopping the code, but right after stopping the code, I can let the macro play or step through it, and the very next debug print picks up the FocusSheet.CodeName, eventhough the only codes in between are debug print and stop. The only difference is that I have activated the VBE window by making it stop there and somehow, it is able to understand how to work from the PERSONAL.XLSB and pull codenames from the ActiveWorkbook eventhough I didn't explicitly name the Actveworkbook in the code.

 

[shg]

You're running your code. Have you tried mine?