Worksheet Reference using Personal.xlsb

jwburritt

New Member
Joined
May 22, 2019
Messages
48
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
As per the thread you referred to ...
You can only refer to a sheet by codename in the workbook in which the sheet exists
 
Last edited:

jwburritt

New Member
Joined
May 22, 2019
Messages
48
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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/
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,115
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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,

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,734
Messages
5,597,809
Members
414,177
Latest member
RezaAkhund

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top