Rename Worksheet Event in Excel

Question

What is the best way to get some VBA code to run when a excel sheet is renamed?

Answer 1

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.