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 "Before Double Click"
End Sub

輸出

雙擊任何單元格時，將向用戶顯示訊息框，如下面的螢幕截圖所示。

工作簿事件

工作簿事件在整個工作簿發生更改時觸發。我們可以透過選擇“ThisWorkbook”並從下拉選單中選擇“工作簿”來新增工作簿事件的程式碼，如下面的螢幕截圖所示。 “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 工作表時，將向用戶顯示一條訊息，如下面的螢幕截圖所示。