Preventing Problems With Worksheet Renaming

This page describes how to write your code in a manner such that errors will not arise if a user renames a worksheet.

Introduction

Once you develop and deploy your Excel-based application, the end users very often do things that they shouldn't do, despite warnings and documentation to the contrary. One of these things is renaming a worksheet. Short of protecting the structure of the workbook, which may prohibit actions that you want to allow to the user, you cannot prevent a user from changing a worksheet name. This may cause code to throw a run time error or cause the code's logic to perform in an unexpected manner.

However, there are a few things you can do to make the code work regardless of whether a user renames a sheet. These measures are not entirely fool-proof. Like most everything else in Excel, a knowledgable and motiviated user can circumvent any sort of protection you may employ. However, the methods described here may be "good enough" to prevent an honest user from making an honest mistake.

When a user renames a worskheet no event procedure is triggered, so there is no event to trap the renaming, let alone cancelling the operation. If you have a sheet name hard coded into your VBA code, the code will fail with an error 9 (subscript out of range) when it attempts to access the sheet by the original name.

Using Code Names

Normally, you access as sheet by its Name property, which is the name that is displayed on the worksheet tab, e.g., Worksheets("Sheet2"). However, the sheet object is known to VBA by its CodeName property. If you don't rename a worksheet, the code name is the same as the sheet name. But when you rename a worksheet, the name changes but the code name remains unchanged. You can see this in the Project Explorer window in the VBA Editor. In the Microsoft Excel Objects "folder" for your project in the project window, you will see an object for each worksheet and one for ThisWorkbook. The first name for each worksheet, outside the parentheses is sheet code name, and the name, which appears on the sheet tab in Excel, is the name within parentheses after the code name. In nearly all circumstances in VBA, you can use the code name anywhere you would normally use Worksheet("SheetName"). For example, the following code will cause an error if the user changes the name of Sheet1 to Sheet One:

Debug.Print Worksheets("Sheet1").Name

This will result in a run time error 9, subscript out of range, since there is no longer an sheet named Sheet1. But the code name of the sheet isn't changed. It remains as Sheet1. Thus, you can use code like:

Debug.Print Sheet1.Name

While a Worksheet has a property to get the CodeName property, the is no such proprety that will give you a Worksheet object from a CodeName property. However, some simple VBA code can provide this functionality.

Function GetWorksheetFromCodeName(CodeName As String) As Worksheet
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        If StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then
            Set GetWorksheetFromCodeName = WS
            Exit Function
        End If
    Next WS
End Function

This procedure takes a string variable containing a CodeName and returns a Worksheet object associated with the code name. You can use this procedure in code like the following:

Dim WS As Worksheet
Set WS = GetWorksheetFromCodeName("Sheet3")
Debug.Print WS.Name

Using Defined Names

You can also employ defined names to circumvent problems that arise when a sheet is renamed. For example, you can create a defined name with:

ThisWorkbook.Names.Add Name:="Summary", _
    RefersTo:=Worksheets("SummarySheet").Range("A1"), Visible:=False

This range will remain intact and valid even if the worksheet SummarySheet is renamed. You get the name of the sheet with code like the following:

Dim WS As Worksheet
Set WS = Application.Names("Summary").RefersToRange.Worksheet
Debug.Print WS.Name

You can wrap this logic into a simple procedure as in the following:

Function GetWorksheetFromName(NameText As String) As Worksheet
    With ThisWorkbook
        Set GetWorksheetFromName = .Names(NameText).RefersToRange.Worksheet
    End With
End Function

You can the call this with code like

Dim WS As Worksheet
Set WS = GetWorksheetFromName("Summary")
Debug.Print WS.Name

where Summary is the defined name.

The variable WS will refer cell on which it was created, even if the name of the worksheet is changed.

Changing The CodeName Of A Worksheet

If you are going to use the code name approach described above, you will quickly find that the default code names make it hard to read code. For example, it isn't clear just what worksheet Sheet1 refers to. The CodeName property of a Worksheet object is read-only; you can't simply assign a new value to the CodeName property. However, you can change the name of the VBComponent of the worksheet. E.g.,

ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "SummarySheet"

Now, you can access the sheet via the new code name:

Debug.Print SummarySheet.Name

In this line of code, Sheet1 is the code name of the sheet (regardless of the name of the sheet), and SummarySheet is the new code name. Of course, once you change the code name of the worksheet, any reference to the original code name (Sheet1 in example above) will fail. The new code named assigned to the VBComponent must begin with a letter and contain up to 31 alphanumeric characters. Spaces are not allowed.

You can manually change the CodeName of a worksheet by selecting the appropriate sheet in the VBA Project window and changing the Name property in the Properties box (press F4 if the Properties window is not visible).

This page last updated: 17-July-2012.