Rename Worksheet Event in Excel

There apparently is no Event to handle this, even using the Application object. How annoying.

I'd probably try to capture it by storing the startup value of the Worksheet and checking it on as many events as possible - which is admittedly a hack.

The following seemed to work for me, Hope it helps.

In the ThisWorkbook module:

Private strWorksheetName As StringPrivate Sub Workbook_Open()    strWorksheetName = shtMySheet.NameEnd SubPrivate Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    Call CheckWorksheetNameEnd SubPrivate Sub Workbook_NewSheet(ByVal Sh As Object)    Call CheckWorksheetNameEnd SubPrivate Sub Workbook_WindowDeactivate(ByVal Wn As Window)    Call CheckWorksheetNameEnd SubPrivate Sub Workbook_SheetActivate(ByVal Sh As Object)    Call CheckWorksheetNameEnd SubPrivate Sub Workbook_SheetDeactivate(ByVal Sh As Object)    Call CheckWorksheetNameEnd SubPrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)    Call CheckWorksheetNameEnd SubPrivate Sub CheckWorksheetName()    'If the worksheet has changed name'    If shtMySheet.Name <> strWorksheetName Then        DoSomething    End IfEnd Sub

Here's one approach. The trick is to trap the events at an application level via a dedicated class. Using the SheetActivate event, store a reference to the active sheet as well as its name. When the sheet is deactiveated (and another activated) compare the name of the sheet reference against the stored string. Here's the class (called CExcelEvents):

Option ExplicitPrivate WithEvents xl As ApplicationPrivate CurrSheet As WorksheetPrivate CurrSheetName As StringPrivate Sub Class_Initialize()    Set xl = Excel.Application    Set CurrSheet = ActiveSheet    CurrSheetName = CurrSheet.NameEnd SubPrivate Sub Class_Terminate()    Set xl = NothingEnd SubPrivate Sub xl_SheetActivate(ByVal Sh As Object)    If CurrSheetName <> CurrSheet.Name Then        Debug.Print "You've renamed the sheet: " & CurrSheetName & " to " & CurrSheet.Name'       Do something here - rename the sheet to original name?    End If    Set CurrSheet = Sh    CurrSheetName = CurrSheet.NameEnd Sub

Instantiate this with a global variable using the Workbook open event:

Public xlc As CExcelEventsSub Workbook_Open()    Set xlc = New CExcelEventsEnd Sub

The example above will trigger only when the user selects another worksheet. If you want more granularity, monitor the Sheet Change event as well.

I know this is an old question but I've recently begun to use Excel's CELL("filename") function which returns details about both file and sheet names.

We can parse the sheet name, using this well-known formula:

=MID(CELL(""filename"", A1),FIND(""]"",CELL(""filename""," A1))+1,255)"

By writing this function to a hidden worksheet, and then monitoring the _Calculate event on that sheet, we can catch any change to the worksheet name.

I had to resort to this method because I needed to share some VBA code with a client, which gave him the possibility to change certain worksheet names programmatically as well as by typing onto the tab. This method captures a sheet name changed event even if it was made in code.

In the skeleton code below, I've just captured the name change for the active worksheet but there's nothing to stop you adding a target worksheet list and adjusting the handling code accordingly.

The code below is in the Workbook code-behind:

Option ExplicitPrivate mSheetNamesWS As WorksheetPrivate mOldSheetName As StringPrivate Sub Workbook_Open()    'Find or create the hidden worksheet    'containing the sheet reference.    On Error Resume Next    Set mSheetNamesWS = Me.Worksheets("SheetNames")    On Error GoTo 0    If mSheetNamesWS Is Nothing Then        'Disable events so that the _calculate event        'isn't thrown.        Application.EnableEvents = False        Set mSheetNamesWS = Me.Worksheets.Add        With mSheetNamesWS            .Name = "SheetNames"            .Visible = xlSheetVeryHidden        End With        Application.EnableEvents = True    End If    'Update the sheet reference.    If TypeOf ActiveSheet Is Worksheet Then        UpdateCellFormula    End IfEnd SubPrivate Sub Workbook_SheetActivate(ByVal Sh As Object)    'Active sheet has changed so update the reference.    If TypeOf ActiveSheet Is Worksheet Then        UpdateCellFormula    End IfEnd SubPrivate Sub UpdateCellFormula()    Dim cellRef As String    'Sense check.    If mSheetNamesWS Is Nothing Then Exit Sub    'The CELL function returns details about    'the file and sheet name of any    'specified range.    'By adding a formula that extracts the    'sheet name portion from the CELL function,    'we can listen for any changes    'of that value in the _calculate event method.    'Disable events to avoid a spurious    '_calculate event.    Application.EnableEvents = False    cellRef = ActiveSheet.Name & "!A1"    With mSheetNamesWS.Range("A1")        .Formula = _            "=MID(CELL(""filename""," & _            cellRef & _            "),FIND(""]"",CELL(""filename""," & _            cellRef & _            "))+1,255)"        mOldSheetName = .Value    End With    Application.EnableEvents = TrueEnd SubPrivate Sub Workbook_SheetCalculate(ByVal Sh As Object)    'Disregard any sheet that isn't our reference sheet.    If Not Sh Is mSheetNamesWS Then Exit Sub    'The reference sheet has recalculated.    'It means the value of the cell containing    'the current sheet name has changed.    'Ergo we have a sheet name change.    'Handle the event here ...    MsgBox "You can't change the name of this sheet!"    Application.EnableEvents = False    ActiveSheet.Name = mOldSheetName    Application.EnableEvents = TrueEnd Sub