excel - 工作表上的 VBA excel 事件为每个工作表打开全局

Question

如何为每个 wokrsheet 触发 onworksheetopen 事件？我需要知道活动工作表的索引号，但是在每个工作表中编写 worksheet_open 事件有点愚蠢。我想在 Workbook 中创建全局函数（Sub）以在每次打开任何工作表时触发事件以获取它的索引号

Sub WorksheetIndex()
Dim table As String
 Select Case ActiveSheet.Index
    Case 4
        table = "Trees"
    Case 5
        table = "Helps"
    Case 6
        table = "Plants"
    Case 7
        table = "Excavation"
    Case 8
        table = "Building"
    Case 9
        table = "Exterier"
    Case 10
        table = "Jobs"
    Case 11
        table = "Irrigation"
    Case 12
        table = "Instalation"
    Case 13
        table = "Systems"
    Case 14
        table = "Electronic"
    Case 15
        table = "Works"
    Case 16
        table = "Example"
    Case 17
        table = "Cars"

    End Select
    
    End If
End Sub

标签： excelvbaworksheet

Answer 1

Event将此代码复制到ThisWorkbook代码模块中，请：

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   MsgBox Sh.Name & " - " & Sh.Index
   'You can use it as you need and eliminate the annoying message...
End Sub