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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,362
Office Version
365
Platform
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,362
Office Version
365
Platform
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
7,846
Office Version
2016
Platform
Windows
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,102,643
Messages
5,488,074
Members
407,622
Latest member
plantaddict

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top