Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Activate sheets by codename from the Personal.xlsb

  1. #1
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Activate sheets by codename from the Personal.xlsb

    How do I activate/access sheets by codename from the Personal.xlsb
    I want it to work for 2 scenarios:
    1) Recently downloaded Excel report in the temporary folder, with one sheet, Worksheet name and codename are both sheet1.

    2) Workbook has been saved and the worksheet name has been changed from Sheet1 but the codename is still Sheet1 and I want to refer to the codename.

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,195
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

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

    You can set an object variable to refer to a particular sheet in any workbook, and changing the sheet name won't affect the object variable; it will still refer to the same sheet.

  3. #3
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    I tried this but it doesn't actually transfer the codename into my macro:
    Code:
    Public Function GetWorksheetFromCodeName( _
          ByVal CodeName As String) As Worksheet
    
    ' Return the worksheet with the requested code name.
       Dim FocusSheet As Object
       
       For Each FocusSheet In ThisWorkbook.Worksheets
          If FocusSheet.CodeName = CodeName Then
             Set GetWorksheetFromCodeName = FocusSheet
             Exit Function
          End If
       Next FocusSheet
    
    End Function
    Then I tried the macro:
    Code:
    Sub Test_Won()
    Dim wsPivot As Excel.Worksheet
    Set wsPivot = GetWorksheetFromCodeName(sht3NURPLE)
    MsgBox wsPivot.Name
    End Sub
    Note: I codnamed Sheet3 as sht3NURPLE and renamed the sheetname as "THIRD" for testing.

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,195
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    sht3NURPLE should be in quotes.

  5. #5
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    Thanks. I actually typed the wrong thing, it was in quotes.
    her is the exact copy/paste:
    Code:
    Public Function GetWorksheetFromCodeName( _
          ByVal CodeName As String) As Worksheet
    
    ' Return the worksheet with the requested code name.
       Dim FocusSheet As Object
       
       For Each FocusSheet In ThisWorkbook.Worksheets
          If FocusSheet.CodeName = CodeName Then
             Set GetWorksheetFromCodeName = FocusSheet
             Exit Function
          End If
       Next FocusSheet
    
    End Function
    Sub Test_Won()
    Dim wsPivot As Excel.Worksheet
    Set wsPivot = GetWorksheetFromCodeName("sht3NURPLE")
    MsgBox wsPivot.CodeName
    End Sub

  6. #6
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    Doesn't work with
    Code:
    MsgBox wsPivot.Name
    either, nothing captures for wsPivot.

    After patiently stepping through the loop of the Function, I realized it did pass the codename to the function, but it is looping through the personal.xlsb. Since it was only selecting "Sheet1" and not looping to a next sheet, I changed all 3 sheetnames and codenames in the test xlsm workbook (keeping "sht3NURPLE"). WHen I still selected Sheet1, I knew it was the personal xlsb (could have put watch on workbook's name as well, I guess).

    Need to activate the workbook and not the Personal.xlsb

  7. #7
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    Had to change
    Code:
    ThisWorkbook.Worksheets
    to
    Code:
    ActiveWorkbook.Worksheets

    Code:
    Public Function GetWorksheetFromCodeName( _
          ByVal CodeName As String) As Worksheet
    
    ' Return the worksheet with the requested code name.
       Dim FocusSheet As Object
       
       For Each FocusSheet In ActiveWorkbook.Worksheets
          If FocusSheet.CodeName = CodeName Then
             Set GetWorksheetFromCodeName = FocusSheet
             Exit Function
          End If
       Next FocusSheet
    
    End Function
    Sub Test_Won()
    Dim wsPivot As Excel.Worksheet
    Set wsPivot = GetWorksheetFromCodeName("sht3NURPLE")
    MsgBox wsPivot.Name
    End Sub

  8. #8
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    Now it works. Thanks.

  9. #9
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    Code:
    Sub Test_Won()
    Dim wsPivot As Excel.Worksheet
    Set wsPivot = GetWorksheetFromCodeName("sht3NURPLE")
    MsgBox wsPivot.CodeName
    More like what I want (will apply this method to another macro that relies on codenames).

  10. #10
    Board Regular
    Join Date
    Nov 2009
    Location
    Los Angeles, CA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate sheets by codename from the Personal.xlsb

    How do I fix this: Code now failing. For some reason the code only works when I run it from the VBE module the first time? If I open the regular Excel workbook with the data I want to manipulate and click the macro quick button, it doesn' twork, but when I go to the code in the personal.xlsb and then click run, it works. After that, I can click the quick button on my quick menu and it does work, just needs to initially launch from the VBE window. HUH?
    Code:
    Sub Parse_Task_Number_and_Report_Cleanup()
    Dim wbHome As Workbook
    Dim wsD As Worksheet 'Source worksheet - "Download"
    Dim wsR As Worksheet 'Results worksheet
    Dim lngFRow As Long 'First row
    Dim lngLRow As Long 'Last row
    Dim lngLCol As Long 'Last column
    Dim MyRow As Long 'First row through input box
    Dim rngTable As Range 'Table data range
    Dim rngDownload As Range 'download area to be copied
    Dim MyTestRange As Range
    Dim oChart_pic As Object
    'Set Headers for new parsed columns
        Dim headerArray()
        Dim Destination As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = True
    Set wbHome = ActiveWorkbook
    'Name and Identify the download sheet to which you first add new data
    ThisWorkbook.Activate
    wbHome.Activate
    Set wsD = GetWorksheetFromCodeName("Sheet1")
    wsD.Name = "Download"
    Here is function:
    Code:
    Public Function GetWorksheetFromCodeName(ByVal CodeName As String) As Worksheet
    ' Return the worksheet with the requested code name.
       Dim FocusSheet As Object
       
       For Each FocusSheet In ActiveWorkbook.Worksheets
          If FocusSheet.CodeName = CodeName Then
             Set GetWorksheetFromCodeName = FocusSheet
             Exit Function
          End If
       Next FocusSheet
    End Function

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
  •