Referencing Worksheets More Effectively! 10

[SBendBuckeye]

Bowers74,

Thanks for an excellent FAQ and discussion. As was noted above, the best time to do this is probably at design time.

Adapting your work above slightly, the below code could be dropped into a global macro file. To use it, just open an Excel sheet, rename the sheets to whatever you want them to be and call the update macro.

A nice additional feature if I had a little more time would be to load all of the Sheet names into a multiselect listbox which would allow me to pick and choose which ones I actually updated.

Option Explicit

Sub UpdateCodeNames()
   Call UpdCdNms(True, True)
End Sub 'UpdateCodeNames

Sub ListCodeNames()
   Call UpdCdNms(False, True)
End Sub 'ListCodeNames

Private Sub UpdCdNms(Optional Update As Boolean = True, _
                     Optional DebugPrint As Boolean)
Dim wks As Worksheet
Dim lngX As Long
Dim strTemp As String
   If DebugPrint Then
      Debug.Print _
         "Sheet#", "Name", "Old CodeName", "New CodeName"
   End If
   With ActiveWorkbook.VBProject
      For Each wks In ActiveWorkbook.Sheets
         lngX = lngX + 1
         strTemp = wks.CodeName
         If Update Then
            .VBComponents(wks.CodeName).Properties
("_CodeName") = wks.Name
         End If
         If DebugPrint Then
            Debug.Print _
               lngX, wks.Name, strTemp, wks.CodeName
         End If
      Next wks
   End With
   Set wks = Nothing
End Sub 'UpdCdNms

Enjoy!


Have a great day!

j2consulting@yahoo.com

 

[SBendBuckeye]

One of the frustrations of working with the CodeName property is that you cannot use it as an implicit index into the Sheets collection if you need to for some reason.

The following code returns the corresponding WorkSheet object for a given CodeName. I wrote it for late binding but you could just as easily have it return a WorkSheet object.

Function GetCodeNameSheet(ByVal MySheetName As String, _
             Optional ByRef MyWorkBook As Object) As Object
Dim wks As Object
    'Use ActiveWorkbook if no Workbook specified
    If MyWorkBook Is Nothing Then
        Set MyWorkBook = ActiveWorkbook
    End If
    For Each wks In MyWorkBook.Sheets
        If wks.CodeName = MySheetName Then
            Set GetCodeNameSheet = wks
            Exit For
        End If
    Next wks
    Set wks = Nothing
End Function

Have a great day!

j2consulting@yahoo.com