Activate sheets by codename from the Personal.xlsb

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
sht3NURPLE should be in quotes.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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
Back
Top