Assigning Worksheet Variable Using Sheet CodeName

[masouder]

I am trying to set a worksheet variable using the worksheet CodeName property which is stored in a cell in the workbook. If I wanted to do this using the worksheet Name variable that is easy as evidenced by the code below, where the sheet Name is stored in cell A1.

Sub Test()
    Dim ws As Worksheet
    
    Dim sSheet As String
    
    sSheet = [A1]
    
    MsgBox Worksheets(sSheet).Name
    
End Sub

If, however, I store the sheet CodeName in cell A1, then no matter what I try I cannot use it to assign a worksheet variable.

Any ideas are appreciated.

 

[earthworm]

Try this

Sub Test()

Dim sheetname As String
sheetname = Worksheets("Sheet1").Range("A1").Value
   
MsgBox sheetname
    
End Sub

 

[johnnyL]

@masouder,

Change your code:

    Dim sSheet As String

to:

    Dim sSheet As Long

Then set A1 to the index number of sheet that you desire.

 

[Alex Blakenburg]

The most common way of getting the sheet if you only have the codename as a string seems to be to use a loop.

Sub getCodeName()

    Dim ws As Worksheet
    Dim sSheet As String
    
    sSheet = [A1]

    For Each ws In ActiveWorkbook.Worksheets
        If ws.CodeName = Range("A1").Value Then
            sSheet = ws.Name
            Exit For
        End If
    Next ws
    
    MsgBox "Sheet Name: " & Worksheets(sSheet).Name & " / CodeName: " & Worksheets(sSheet).CodeName

End Sub

 

[masouder]

Thank you all for the replies. Unfortunately, the first suggestion (earthworm) does not work because it requires the sheet Name property, not the CodeName property. Similarly, the second suggestion (johhnyL) also won't work because it relies on the Index property.

Alex, I suspected that I would have to use a looping routine based on other posts, but was hoping that there was another way. Unless anyone else has a suggestion on how to set the worksheet variable using on the CodeName property, I guess I'll have to use the loop.

 

[RoryA]

If you have trusted access to the vbproject, you can use:

Set ws = ThisWorkbook.Sheets(ThisWorkbook.VBProject.VBComponents(sSheet).Properties("Name").Value)

 

[masouder]

RoryA, that worked, thank you.

 

[Alex Blakenburg]

If you have trusted access to the vbproject, you can use:

Set ws = ThisWorkbook.Sheets(ThisWorkbook.VBProject.VBComponents(sSheet).Properties("Name").Value)

I think we should add that turning on "trusted access to the vbproject" is not a recommended setting from a security perspective and you can't count on your end user having it turned on.

 

[johnnyL]

The problem I am encountering is that it doesn't seem to be possible to use a variable/Cell refernce as the codename, without using the previously mentioned security route.

msgbox TheCodeNameOfSheet.name

That gives the sheet name, but I can't find a way to use a variable for the codename.

 

[RoryA]

Should we also add that enabling macros is not recommended from a security perspective?