Following the approach in Change Worksheet Codename
I found that the object can not be called with its new name in the same execution.
Consider the code below for a standard worksheet, the first Codename change does nothing, but confirmes the code,
The second iteration fails with variable not set.
It looks as if they compiler trips as the new object is not available yet at compile time.
Anyone know a way to solve this?
I found that the object can not be called with its new name in the same execution.
VBA Code:
Option Explicit
Private Sub test_codename()
Dim ws As Worksheet
Set ws = Sheets(1)
ws.[_CodeName] = "Sheet1"
Set ws = Sheets("Sheet1")
Debug.Print ws.CodeName
Debug.Print Sheet1.CodeName
Sheet1.Activate
Set ws = Sheets(1)
ws.[_CodeName] = "CodeNameTestTwo"
Set ws = Sheets(1)
Debug.Print ws.CodeName
Debug.Print CodeNameTestTwo.CodeName
CodeNameTestTwo.Activate
End Sub
Consider the code below for a standard worksheet, the first Codename change does nothing, but confirmes the code,
The second iteration fails with variable not set.
It looks as if they compiler trips as the new object is not available yet at compile time.
Anyone know a way to solve this?