Here's an interesting subject: worksheet rename event. As we all know, Excel comes with various built-in events such as Worksheet_BeforeDoubleClick, Worksheet_SelectionChange, Worksheet_Change, etc. However, we do not have an event to handle the renaming of a worksheet, something like Worksheet_AfterRename, or Worksheet_Rename.
This week I got a question about a worksheet rename event. Here's my first attempt at handling this event (place the code in ThisWorkbook module):
Private mstr_ActiveSheetPreviousName As String
Private lng_DeactivatedSheetIndex As Long
Private mstr_ActiveSheetName As String
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lng_DeactivatedSheetIndex = Sh.Index
SheetNameChange 0
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
SheetNameChange 1
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
SheetNameChange 2
End Sub
Sub SheetNameChange(ByVal lngCaller As Long)
Select Case lngCaller
Case 0
If lng_DeactivatedSheetIndex <> ThisWorkbook.ActiveSheet.Index Then
If ThisWorkbook.Sheets(lng_DeactivatedSheetIndex).Name <> mstr_ActiveSheetPreviousName Then
NameChanged mstr_ActiveSheetPreviousName, ThisWorkbook.Sheets(lng_DeactivatedSheetIndex).Name
mstr_ActiveSheetPreviousName = mstr_ActiveSheetName
End If
End If
Case 1
mstr_ActiveSheetPreviousName = ThisWorkbook.ActiveSheet.Name
mstr_ActiveSheetName = ThisWorkbook.ActiveSheet.Name
Case 2
mstr_ActiveSheetName = ThisWorkbook.ActiveSheet.Name
If mstr_ActiveSheetName <> mstr_ActiveSheetPreviousName Then
NameChanged mstr_ActiveSheetPreviousName, mstr_ActiveSheetName
mstr_ActiveSheetPreviousName = mstr_ActiveSheetName
End If
End Select
End Sub
Sub NameChanged(ByVal strOldName As String, ByVal strNewName As String)
MsgBox "The worksheet name changed from '" & strOldName & "' to '" & strNewName
End Sub
I have given too thought to it, so it is really just an attempt. If any of you have a better solution or wants to make suggestion, please drop me a line either by e-mail or through a comment.