VBA事件

在VBA中，要手動更改單元格或單元格值範圍時，可以觸發事件驅動的編程。 更改事件可能會使事情變得更容易，但您可以非常快速地結束一個完整的格式化頁面。VBA中有兩種事件 -

• 工作表事件
• 工作簿事件

工作表事件

工作表事件在工作表中發生更改時被觸發。 它是通過右鍵單擊工作表選項卡並選擇“查看代碼”，然後粘貼代碼來創建的。

用戶可以選擇這些工作表中的每一個，並從下拉列表中選擇“工作表”以獲取所有支持的工作表事件的列表。

以下是可以由用戶添加的支持的工作表事件。

Private Sub Worksheet_Activate()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_Calculate()
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Deactivate()
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

例子

在這個示例中，只需要雙擊之前顯示一條消息。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   MsgBox ("這是一個雙擊事件觸發提示")
End Sub

雙擊任何單元格後，消息框將顯示給用戶，如以下螢幕截圖所示。

工作簿事件

工作簿事件總是在工作簿發生更改時觸發的。可以通過選擇“ThisWorkbook”並從下拉列表中選擇“workbook”來添加工作簿事件的代碼，如下圖所示。

立即將Workbook_open子過程顯示給用戶，如以下螢幕截圖所示。

以下是可以由用戶添加的受支持的工作簿事件。

Private Sub Workbook_AddinUninstall()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Private Sub Workbook_Deactivate()
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Private Sub Workbook_WindowResize(ByVal Wn As Window)

例子

只需要向用戶顯示一條消息，無論何時創建新的工作表，都會成功創建新的工作表。

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   MsgBox "New Sheet Created Successfully"
End Sub

在創建一個新的excel工作表後，會向用戶顯示一條消息，如以下螢幕截圖所示。