Rename Worksheet Event in Excel

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 Explicit

Private WithEvents xl As Application

Private CurrSheet As Worksheet
Private CurrSheetName As String


Private Sub Class_Initialize()
    Set xl = Excel.Application
    Set CurrSheet = ActiveSheet
    CurrSheetName = CurrSheet.Name
End Sub

Private Sub Class_Terminate()
    Set xl = Nothing
End Sub



Private 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.Name
End Sub

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

Public xlc As CExcelEvents

Sub Workbook_Open()
    Set xlc = New CExcelEvents
End 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.

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 String

Private Sub Workbook_Open()
    strWorksheetName = shtMySheet.Name
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call CheckWorksheetName
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Call CheckWorksheetName
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Call CheckWorksheetName
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Call CheckWorksheetName
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Call CheckWorksheetName
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call CheckWorksheetName
End Sub

Private Sub CheckWorksheetName()
    'If the worksheet has changed name'
    If shtMySheet.Name <> strWorksheetName Then

        DoSomething

    End If
End Sub