Worksheet Reference using Personal.xlsb
Results 1 to 7 of 7

Thread: Worksheet Reference using Personal.xlsb
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Worksheet Reference using Personal.xlsb

    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-...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

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Worksheet Reference using Personal.xlsb

    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 by Yongle; Jul 19th, 2019 at 05:53 PM.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Reference using Personal.xlsb

    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?

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Worksheet Reference using Personal.xlsb

    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-works...her-workbooks/
    Last edited by Yongle; Jul 20th, 2019 at 03:30 AM.

  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,241
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Worksheet Reference using Personal.xlsb

    Quote Originally Posted by jwburritt View Post
    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 by Jaafar Tribak; Jul 20th, 2019 at 03:31 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Reference using Personal.xlsb

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

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Reference using Personal.xlsb

    Works great, Thanks!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •