VBA事件

VBA 事件

VBA，事件驅動編程時可以手動更改單元格值的單元格或單元格區域被觸發。更改事件可能會使事情變得更容易，但可以很快結束了一個完全格式化的頁面。有兩種類型的事件。

• 工作表事件

• 工作簿活動

工作表事件

工作表事件被觸發時，在工作表中有變化。表標籤上執行右鍵單擊，選擇「view code」，然後粘貼代碼創建的。

用戶可以選擇那些工作表中的每一個，並從下拉列表中選擇「工作表」下去把所有支持工作表的事件列表。

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

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'，如下所示添加爲工作簿的事件的代碼。立即 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工作表的消息顯示給用戶，如下所示。