Rowland Hamilton
New Member
How do I make this work?
I want this code to work on any actual workbook I open and launch by a quick access button 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.
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 (note: code will work in any workbook):
The results of the debug.prints in the Immediate window are:
If I open a fresh instance of excel and launch this code outside of the VBE, when it stops at the bold Stop, I notice that the "First" FocusSheet.CodeName is not populated, 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.prints, stop and starting the loop but not progressing so it should have been the same result. 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 without having the Activeworkbook explicitely named in the code.
WHY? How come it can't work outside of the VBE the first time?
After the first time it does work running through the VBE window, it will work if launched outside of it from a button, or however, and on any new workbook I open, as long as I don't close Excel and start over again, then it breaks like it does, until I go into the VBE window again. If I program the stop after the codename should have been captured, it will not work, I have to get into it before the critical moment when it captures the codename.
I want this code to work on any actual workbook I open and launch by a quick access button 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.
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 (note: code will work in any workbook):
Code:
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
Debug.Print "First-FocusSheet.CodeName =" & FocusSheet.CodeName
Stop
If FocusSheet.CodeName = CodeName Then
Debug.Print "Match-FocusSheet.CodeName =" & FocusSheet.CodeName
Debug.Print "Match-CodeName =" & CodeName
'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 SubThe results of the debug.prints in the Immediate window are:
If I open a fresh instance of excel and launch this code outside of the VBE, when it stops at the bold Stop, I notice that the "First" FocusSheet.CodeName is not populated, 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.prints, stop and starting the loop but not progressing so it should have been the same result. 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 without having the Activeworkbook explicitely named in the code.
WHY? How come it can't work outside of the VBE the first time?
After the first time it does work running through the VBE window, it will work if launched outside of it from a button, or however, and on any new workbook I open, as long as I don't close Excel and start over again, then it breaks like it does, until I go into the VBE window again. If I program the stop after the codename should have been captured, it will not work, I have to get into it before the critical moment when it captures the codename.
Last edited: