Excel Macro: Rename Worksheet Events in Excel

Is there  a event to handle the renaming of a worksheet? Unfortunately, the Excel object model does not provide any events for such tasks. The only event I can find in Excel that fires when a sheet is renamed is Application.AfterCalculate event.

Rename Worksheet Events in Excel

Example 1

The following example renames the sheet to the original name when the user changes the sheet name.

Put the following code in the ThisWorkbook object.

'-------------------- ThisWorkbook --------------------
Public WithEvents App As Application
Public CurrentSheet As Worksheet
Public CurrentSheetName As String

Private Sub Workbook_Open()
    Set App = Application
    Set CurrentSheet = ActiveSheet
    CurrentSheetName = CurrentSheet.Name
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
    Set CurrentSheet = Sh
    CurrentSheetName = CurrentSheet.Name
End Sub

Private Sub App_AfterCalculate()
    ' DO WHATEVER YOU WANT After Calculate.
    If CurrentSheetName <> CurrentSheet.Name Then
    
        MsgBox "Please do not change the sheet name!"
        
        ' Do something here - E.g., rename the sheet to original name.
        MsgBox "Rename the sheet to original name."
        Application.EnableEvents = False    'Disable events so we can change the sheet name
        CurrentSheet.Name = CurrentSheetName
        Application.EnableEvents = True
        
    End If
End Sub

Example 2

The following example calls a subroutine when the user changes the sheet name. Download this example.

Put the following code in the ThisWorkbook object.

'-------------------- ThisWorkbook --------------------
Public WithEvents App As Application
Public CurrentSheet As Worksheet
Public CurrentSheetName As String

Private Sub Workbook_Open()
    Set App = Application
    Set CurrentSheet = ActiveSheet
    CurrentSheetName = CurrentSheet.Name
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
    Set CurrentSheet = Sh
    CurrentSheetName = CurrentSheet.Name
End Sub

Private Sub App_AfterCalculate()
    ' DO WHATEVER YOU WANT After Calculate.
    If CurrentSheetName <> CurrentSheet.Name Then
    
        MsgBox "The name of the active sheet has changed! Call RunAfterCalculate subroutine."
        CurrentSheetName = CurrentSheet.Name   'don't forget
        Call RunAfterCalculate
        
    End If
End Sub

Make sure the subroutine is run only once, put the following code in the Module object.

Option Explicit
Public RunningAfterCalculate As Boolean

Public Sub RunAfterCalculate()
    If Not RunningAfterCalculate Then
        RunningAfterCalculate = True

        ' DO WHATEVER YOU WANT AfterCalculate     
        MsgBox "Call RunAfterCalculate Once ..."

        RunningAfterCalculate = False
    End If
End Sub

How to Use This Macro