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 工作表时，将向用户显示一条消息，如下面的屏幕截图所示。