Worksheet Reference using Personal.xlsb

[jwburritt]

Hello: I am building a macro that works with multiple other files. It is contained in the personal.xlsb file. However, when I refer to the worksheets in the other files, I'm able to use the Name property -- for example "New Sheet" -- without a problem. But when I try to use the CodeName property -- for example, "Sheet1" -- it doesn't work.

I have a feeling it has to do with the personal.xlsb file, but I'm not sure. Any ideas?

I did fine something here (https://www.mrexcel.com/forum/excel...b.html?highlight=Personal.xlsb+sheet+codename) but I still want any other info.

I'm trying to use both the CodeName property and the personal.xlsb as a best practices. Am I on the right track here?

Thanks so much in advance for any help!

Wayne

 

[Yongle]

As per the thread you referred to ...

You can only refer to a sheet by codename in the workbook in which the sheet exists

 

[jwburritt]

Thank you for the quick reply. Would there be a way to use variables so that I can keep the macro in the personal.xlsb and still use the codename?

 

[Yongle]

1. It is IMPOSSIBLE to use notation Workbooks("FileWithSheetCodeName.xlsm").SheetCodeName.Range("A1") to refer to a worksheet in another workbook

2. But a sheet variable can be set in Personal.xlxb based on the sheet codename in another workbook
- the function loops through all sheets in the target workbook until it finds the one with the correct codename
- see Test1 where variable ABC has been set as sheet in "FileWithSheetCodeName.xlsm" with codename "CodeNameX"

3. Taking things one step further, set worksheet variable with the same name as the codename
- see Test2 below where the IMPOSSIBLE appears POSSIBLE
- CodeNameX.Range("A1") can be used after all
- of course, this is a "cloak and mirrors" job and is not the same notation
Important -avoid risk of code conflict by renaming the codenames of sheets in Personal.xlsb to Peronal1, Personal2 etc

Function GetWsFromCodeName(wb As Workbook, CodeName As String) As Worksheet
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        If ws.CodeName = CodeName Then
            Set GetWsFromCodeName = ws
            Exit For
        End If
    Next ws
End Function

Sub Test1()
    Dim ABC As Worksheet
    Set ABC = GetWsFromCodeName(Workbooks("FileWithSheetCodeName.xlsm"), "CodeNameX")
    
    MsgBox ABC.Name & vbCr & ABC.Parent.Name    
    ABC.Range("A1") = "TestValue 1"  
End Sub

Sub Test2()
    Dim CodeNameX As Worksheet
    Set CodeNameX = GetWsFromCodeName(Workbooks("FileWithSheetCodeName.xlsm"), "CodeNameX")    

    MsgBox CodeNameX.Name & vbCr & CodeNameX.Parent.Name  
    CodeNameX.Range("A2) = "TestValue 2"
End Sub

Credit for the function https://yoursumbuddy.com/using-worksheet-codenames-in-other-workbooks/

 

[Jaafar Tribak]

Thank you for the quick reply. Would there be a way to use variables so that I can keep the macro in the personal.xlsb and still use the codename?

You could write a function in the personal workbook that has two arguments one for the workbook and one for the worksheet codename and returns the worksheet.

Same as posted by RoryA,

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

 

[jwburritt]

Thank you so much! I'll give these a try and get back to you ASAP.

 

[jwburritt]

Works great, Thanks!