在Excel中重命名工作表事件

问题描述

解决方案

这是一种方法。诀窍是通过一个专门的类来捕获应用程序级的事件。使用SheetActivate事件，存储对活动工作表的引用及其名称。当表格被取消活动（和另一个激活）时，将表格引用的名称与存储的字符串进行比较。这是类（称为CExcelEvents）：

  Option Explicit 
 
私有WithEvents xl作为应用程序
 
私有CurrSheet作为工作表
私有CurrSheetName作为字符串
 
 
私有子类Class_Initialize（）
设置xl = Excel.Application 
设置CurrSheet = ActiveSheet 
 CurrSheetName = CurrSheet.Name 
 End Sub 
 
 Private Sub Class_Terminate（）
 Set xl = Nothing 
 End Sub 
 
 
 
私有子xl_SheetActivate（ByVal Sh As Object）
如果CurrSheetName<> CurrSheet.Name然后
 Debug.Print你已经重命名了这个工作表：& CurrSheetName& 到& CurrSheet.Name 
'在这里做某事 - 将表格重命名为原始名称？ 
 End If 
 
设置CurrSheet = Sh 
 CurrSheetName = CurrSheet.Name 
 End Sub 
  

使用Workbook打开事件为全局变量实例化：

  Public xlc作为CExcelEvents 
 
 Sub Workbook_Open（）
设置xlc =新的CExcelEvents 
 End Sub 
  

以上示例仅在用户选择其他工作表时触发。如果您想要更细粒度，请同时监控工作表变更事件。

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

解决方案

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.

这篇关于在Excel中重命名工作表事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！