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

I'm eagerly awaiting an answer to this because I haven't figured it out after much searching. There is no rename event on a worksheet that I have found, so you are forced to have an alternative approach.

The best one I have seen (which is awful) is to prohibit rename on the sheets by making them read-only or invisible, and then provide your own toolbar or button that does the rename. Very ugly and users hate it.

I have also seen applications that disable the rename menu item in the office toolbar, but that doesn't prevent double-clicking the tab and renaming there. Also very ugly and users hate it.

Good luck, I hope someone comes up with a better answer.

Answer 2

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

Answer 3

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.