web 2.0


Excel Worksheet Rename Event

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.

Tags: , , ,

Microsoft Excel | Microsoft Excel - VBA

Comments

excelperfect People's Republic of China, on 5/13/2009 12:52:41 AM Said:

excelperfect

Very Good!
But I thank you should add the following code:
Private Sub Workbook_Open()
    mstr_ActiveSheetPreviousName = ThisWorkbook.ActiveSheet.Name
End Sub

excelperfect.com , on 5/13/2009 1:16:27 AM Said:

pingback

Pingback from excelperfect.com

Excel工作表重命名事件 | 完美Excel

Robert Martim Brazil, on 6/16/2009 8:55:02 PM Said:

Robert Martim

Thanks for the comment. Very much appreciated. It is a good idea to start the variable... I had done it originally, but I forgot to copy over to the article.

Comments are closed