学习笔记 by llj1998

4.Excel事件

1.让Excel自动响应你的行为

Excel VBA有一系列预定义的事件，Workbook_Open()就是其中之一，把以下代码写入到ThisWorkbook模块，每次打开Excel文件都会弹出消息框

写入到Sheet模块无效，因为Workbook_Open()是一个工作簿级别的事件过程，Sheet1只能响应工作表级别的事件，工作表模块不能包含Workbook_开头的过程名

Private Sub Workbook_Open()
    MsgBox "欢迎使用本工作簿！"
End Sub

如果想在每次点开某个工作表都弹出提示，可以在对应的工作表模块中写入以下代码：

Private Sub Worksheet_Activate()
    MsgBox "欢迎来到Sheet1！"
End Sub

其他常见的预定义事件还有：

Workbook_BeforeClose()：工作簿关闭前触发

Worksheet_Change()：工作表内容改变时触发

Worksheet_SelectionChange()：选择单元格改变时触发

事件，VBA里的自动开关

事件时Excel中发生的特定动作或情况，它们可以触发我们预现编写的代码

事件包括但不限于：
工作簿事件：如打开工作簿(Open)、保存工作簿(BeforeSave)、关闭工作簿(BeforeClose)等。

工作表事件：如激活工作表(Activate)、更改单元格(Change)、选择单元格(SelectionChange)等。

应用程序事件：如新建工作簿(NewWorkbook)、工作簿计算完成(WorkbookCalculate)等。

事件的作用：
自动化：用户无需手动触发，代码可以在特定情况下自动执行

交互性：可以根据用户的操作做出及时响应

数据验证：可以在数据输入或更改时进行检查和炎症

事件过程

当某个事件发生后自动运行的过程称为事件过程，事件过程也是Sub过程。

事件过程必须写在特定对象所在的模块中，而且只有过程所在的模块里的对象才能触发这个事件（每种事件都与特定的对象（如工作簿、工作表）相关联，因此必须放在正确的模块中）

编写事件过程

事件过程的过程名由Excel自动设置，以【对象名称_事件名称】的形式存在，不能更改。

想编写关于哪个对象的事件过程，就在【工程资源管理器】中双击该对象所在的模块【代码窗口】

如果想写这样一个程序，当激活Sheet1时，自动完成某些操作或计算，就双击进入Sheet1模块，在代码窗口上方，左边的下拉菜单栏选择Sheet1，右边的下拉菜单选择Active事件，希望程序完成什么操作或计算，就把响应的代码写在Sub与End Sub之间，之后每次切换大该工作表，都会执行设置的代码。

2.Worksheet事件

Worksheet事件是发生在Worksheet对象里的事件，Worksheet事件过程必须写在相应的Worksheet对象里，只有过程所在的Worksheet对象里的操作才能触发该时间。

常用的Worksheet事件

Worksheet_Change 工作表中的单元格内容发生变化

Worksheet_Change告诉Excel，当过程所在的工作表的单元格发生更改时自动运行程序过程，程序必须写在相应的工作表对象里

用途：数据验证、动态更新、记录修改等

变量Target是程序运行的参数，代表工作表中被更改的但愿那个

' 用途：数据验证 - 确保第一列只输入数字
' 这个事件可以用于实时数据验证，防止用户输入错误的数据类型
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 检查是否更改的是第一列的单元格
    If Target.Column = 1 Then
        ' 检查输入的值是否为数字
        If Not IsNumeric(Target.Value) Then
            ' 如果不是数字，显示警告消息
            MsgBox "请输入数字！"
            ' 清空单元格内容
            Target.Value = ""
        End If
    End If
End Sub
Worksheet_SelectionChange 用户改变选定的单元格或区域

当工作表选中的单元格或区域发生改变时触发

' 用途：动态信息显示 - 实时显示用户当前选中的单元格
' 这个事件可以用于创建交互式工作表，帮助用户追踪他们在工作表中的位置
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 在A1单元格显示当前选中的单元格地址
    Me.Range("A1").Value = "当前选中: " & Target.Address
    ' 注：Me 表示当前工作表，可以省略
End Sub
Worksheet_Activate 工作表被激活（切换到该工作表）

用途：欢迎信息、初始化设置、更新数据等


' 用途：初始化和欢迎信息 - 当用户进入工作表时执行必要的设置和更新
' 这个事件可以用于显示欢迎信息，更新数据，或执行其他必要的初始化操作
Private Sub Worksheet_Activate()
    ' 显示欢迎消息，包含当前工作表的名称
    MsgBox "欢迎来到" & ActiveSheet.Name & "工作表！"
End Sub
Worksheet_Deactivate 当用户离开工作表

当活动工作表转为非活动工作表（用户离开该工作表）时触发

用途：保存更改、清理临时数据、显示提醒等

' 用途：保存提醒 - 在用户离开工作表时提醒保存更改
' 这个事件可以用于防止用户无意中丢失重要的更改
Private Sub Worksheet_Deactivate()
    ' 检查A1单元格是否有内容
    If Range("A1").Value <> "" Then
        ' 如果A1不为空，提醒用户保存更改
        MsgBox "请记得保存您的更改！"
    End If
End Sub
Worksheet_Calculate 当工作表完成计算

用途：在复杂计算后更新图表、触发其他操作等

Private Sub Worksheet_Calculate()
    Application.StatusBar = "计算完成于 " & Now
End Sub
Worksheet_BeforeDoubleClick 在用户双击工作表的单元格之前

用途：自定义双击行为、阻止默认的双击编辑等

' 用途：自定义双击行为 - 为特定区域的双击操作定义特殊行为
' 这个事件可以用于创建自定义的交互功能，如显示额外信息或触发特定操作
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 检查双击的是否是第一列的单元格
    If Target.Column = 1 Then
        ' 显示被双击的单元格地址
        MsgBox "你双击了第一列的单元格：" & Target.Address
        ' 设置 Cancel 为 True 以阻止默认的双击编辑行为
        Cancel = True
    End If
End Sub
Worksheet_BeforeRightClick 在用户右键单击工作表的单元格之前

用途：自定义右键菜单、阻止默认的右键菜单等

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row = 1 Then
        MsgBox "请不要对标题行使用右键菜单"
        Cancel = True ' 阻止默认的右键菜单
    End If
End Sub

Worksheet事件列表

Worksheet对象一共有9个事件可使用：

事件名称触发条件常见用途
Activate工作表被激活时初始化设置，显示欢迎信息，更新数据
BeforeDoubleClick用户双击单元格之前自定义双击行为，显示额外信息，触发特定操作
BeforeRightClick用户右键单击单元格之前自定义右键菜单，保护特定区域
Calculate工作表完成计算时更新依赖于计算结果的内容，通知用户计算完成
Change工作表中的单元格内容发生变化时数据验证，动态更新，记录修改
Deactivate用户离开该工作表时保存更改，清理临时数据，显示提醒
FollowHyperlink用户点击工作表中的超链接时记录链接点击，执行自定义操作
PivotTableUpdate数据透视表更新完成后格式化数据透视表，更新相关图表
SelectionChange用户改变选定的单元格或区域时动态显示信息，更新状态栏，控制其他单元格的显示

3.Workbook事件

Workbook事件是发生在Workbook对象里的事件，必须在ThisWorkbook模块中定义，使用【Workbook_】前缀命名。

Workbook_Open 工作簿被打开

用途：初始化设置、显示欢迎信息、加载数据等

' 用途：在工作簿打开时执行初始化操作
Private Sub Workbook_Open()
    ' 显示欢迎信息
    MsgBox "欢迎使用本工作簿！"
    
    ' 初始化设置，例如设置特定单元格的值
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "上次打开时间：" & Now()
    
    ' 调用自定义函数加载数据
    Call LoadData
End Sub
Workbook_BeforeClose 工作簿即将关闭时

用途：保存更改、清理临时文件、显示提醒等

' 用途：在工作簿关闭前执行清理操作
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 询问用户是否确定要关闭
    Dim response As Integer
    response = MsgBox("确定要关闭工作簿吗？", vbYesNo + vbQuestion, "确认关闭")
    
    If response = vbNo Then
        Cancel = True ' 取消关闭操作
    Else
        ' 执行清理操作
        Call CleanupTempFiles
        ThisWorkbook.Save ' 保存工作簿
    End If
End Sub
Workbook_SheetActivate 工作簿中的任何工作表被激活

用途：更新UI，执行特定于该工作表的操作等

' 用途：响应工作表激活事件
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ' 更新状态栏显示当前激活的工作表名称
    Application.StatusBar = "当前工作表: " & Sh.Name
    
    ' 如果激活的是特定工作表，执行额外操作
    If Sh.Name = "数据表" Then
        Call RefreshData
    End If
End Sub
Workbook_SheetChange 工作簿中任何工作表的内容发生变化时

用途：跟踪更改，更新相关数据等

‘ 用途：响应工作表内容变化

' ====================================================================================================
' 模块名称：ThisWorkbook
' 
' 功能描述：
' 这个模块包含了工作簿级别的事件处理程序，主要用于跟踪和记录工作表的变更。
' 主要功能包括：
' 1. 监听工作表内容的变化
' 2. 将变更信息记录到专门的日志工作表中
' 3. 防止事件循环和重复触发
' 4. 提供调试信息输出
' 5. 包含一个手动测试过程以验证功能
'
' 注意事项：
' - 本代码应放在ThisWorkbook模块中
' - 需要注意事件循环和性能问题
' - 使用Debug.Print输出有助于排查问题
' ====================================================================================================

Option Explicit  ' 强制声明所有变量

' 用于防止事件循环的标志
Private isRunning As Boolean

' 用途：响应工作表内容变化
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' 防止事件循环
    If isRunning Then Exit Sub  ' 如果已经在运行，则退出
    isRunning = True  ' 设置运行标志
    
    On Error GoTo ErrorHandler  ' 设置错误处理
    
    ' 暂时禁用事件，防止在处理过程中触发其他事件
    Application.EnableEvents = False
    
    ' 输出调试信息：事件触发时间
    Debug.Print "Workbook_SheetChange triggered: " & Now()
    ' 输出调试信息：变更的工作表和单元格范围
    Debug.Print "Sheet: " & Sh.Name & ", Range: " & Target.Address
    
    ' 声明日志工作表对象
    Dim logSheet As Worksheet
    On Error Resume Next  ' 临时忽略错误
    Set logSheet = ThisWorkbook.Sheets("变更日志")  ' 尝试获取日志工作表
    On Error GoTo ErrorHandler  ' 恢复错误处理
    
    ' 如果日志工作表不存在，则创建一个新的
    If logSheet Is Nothing Then
        Set logSheet = ThisWorkbook.Sheets.Add  ' 添加新工作表
        logSheet.Name = "变更日志"  ' 设置工作表名称
        ' 添加表头
        logSheet.Range("A1:C1").Value = Array("时间", "工作表", "单元格地址")
    End If
    
    ' 记录日志
    With logSheet
        Dim nextRow As Long
        ' 找到下一个空行
        nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        ' 记录时间
        .Cells(nextRow, 1).Value = Now()
        ' 记录工作表名称
        .Cells(nextRow, 2).Value = Sh.Name
        ' 记录变更的单元格地址
        .Cells(nextRow, 3).Value = Target.Address
    End With
    
    ' 输出调试信息：确认日志已添加
    Debug.Print "Log entry added successfully"

CleanExit:
    ' 重新启用事件
    Application.EnableEvents = True
    ' 重置运行标志
    isRunning = False
    Exit Sub

ErrorHandler:
    ' 输出错误信息
    Debug.Print "Error in Workbook_SheetChange: " & Err.Description
    Resume CleanExit  ' 跳转到清理代码
End Sub

' 手动触发测试过程
Public Sub TestSheetChange()
    ' 修改第一个工作表的A1单元格，触发SheetChange事件
    ThisWorkbook.Sheets(1).Range("A1").Value = "测试 " & Now()
    ' 显示测试完成消息
    MsgBox "测试完成。请检查'变更日志'工作表和即时窗口。", vbInformation
End Sub
Workbook_NewSheet 当新工作表被添加到工作簿

用途：初始化新工作表、应用标准格式等

' 用途：为新添加的工作表应用标准格式
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    ' 设置标准标题
    Sh.Range("A1:D1").Value = Array("日期", "描述", "金额", "类别")
    Sh.Range("A1:D1").Font.Bold = True
    
    ' 应用标准格式
    Sh.Columns("A:D").AutoFit
    Sh.Range("C:C").NumberFormat = "$#,##0.00"
End Sub

Workbook事件列表

事件名称触发条件常见用途
Activate工作簿被激活时更新界面，刷新数据
AddinInstall工作簿作为加载项安装时初始化加载项设置
AddinUninstall工作簿作为加载项卸载时清理加载项相关数据
AfterSave工作簿保存后记录日志，同步数据
AfterXmlExportXML数据导出后处理导出后的操作
AfterXmlImportXML数据导入后处理导入的数据
BeforeClose工作簿关闭前保存设置，清理资源
BeforePrint打印前设置打印区域，调整格式
BeforeSave工作簿保存前执行数据验证，更新计算
BeforeXmlExportXML数据导出前准备要导出的数据
BeforeXmlImportXML数据导入前准备导入操作
Deactivate工作簿取消激活时保存临时状态
NewChart新建图表时设置图表属性
NewSheet新建工作表时设置新表格式，添加默认内容
Open工作簿被打开时初始化设置，加载数据
PivotTableCloseConnection数据透视表关闭连接时清理资源
PivotTableOpenConnection数据透视表打开连接时刷新数据
RowsetComplete查询完成时处理查询结果
SheetActivate工作表被激活时更新界面，刷新数据
SheetBeforeDoubleClick工作表单元格被双击前自定义双击行为
SheetBeforeRightClick工作表单元格被右击前自定义右键菜单
SheetCalculate工作表重新计算时执行自定义计算
SheetChange工作表内容改变时记录变更，触发计算
SheetDeactivate工作表取消激活时保存临时数据
SheetFollowHyperlink单击超链接时记录访问历史
SheetPivotTableAfterValueChange数据透视表值更改后更新相关数据
SheetPivotTableBeforeAllocateChanges数据透视表分配更改前准备数据更新
SheetPivotTableBeforeCommitChanges数据透视表提交更改前验证更改
SheetPivotTableBeforeDiscardChanges数据透视表放弃更改前确认操作
SheetPivotTableChangeSync数据透视表同步更改时同步相关数据
SheetPivotTableUpdate数据透视表更新时刷新关联图表或数据
SheetSelectionChange选中单元格改变时动态显示信息
Sync工作簿同步时处理同步相关操作
WindowActivate工作簿窗口激活时更新状态栏
WindowDeactivate工作簿窗口取消激活时暂停实时更新
WindowResize工作簿窗口大小改变时调整界面布局

4.别样的自动化

MouseMove事件

在【开发工具-插入】中，拖入ActiveX类的按钮，右键该按钮，可以设置该按钮的属性，包括名称（重要）、字体、大小、背景图等

将该按钮的名称改为【cmd】，右键该按钮选择“查看代码”，在代码窗口中为该按钮添加MouseMove事件，完成后，退出设计模式即可运行代码

' MouseMove 事件处理程序
' 当鼠标在控件上移动时触发
Private Sub cmd_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    Dim l As Integer, t As Integer  ' 声明变量用于存储随机生成的位置偏移量
    
    ' 生成随机数来计算新的左侧位置
    ' Rnd() 生成 0 到 1 之间的随机小数
    ' 公式解释：(0~1 * 10 + 125) * (0~1 * 3 + 1) - 2 生成一个 123 到 405 之间的随机数
    l = Int(Rnd() * 10 + 125) * (Int(Rnd() * 3 + 1) - 2)
    
    ' 生成随机数来计算新的顶部位置
    ' 公式解释：(0~1 * 10 + 30) * (0~1 * 3 + 1) - 2 生成一个 28 到 120 之间的随机数
    t = Int(Rnd() * 10 + 30) * (Int(Rnd() * 3 + 1) - 2)
    
    ' 更新控件的垂直位置（Top属性）
    cmd.Top = cmd.Top + t
    
    ' 更新控件的水平位置（Left属性）
    cmd.Left = cmd.Left + l
End Sub

不是事件的事件

除了对象的事件，Application对象还有两种方法，可以像事件一样让程序自动运行

Application对象的OnKey方法

OnKey方法告诉Excel，当在键盘上按下指定键或组合键时自动运行程序

语法：Application.OnKey(Key, [Procedure])

key是一个字符串，表示要补货的按键或按键组合

Procedure是个可选参数，也是一个字符串，表示要执行的宏名称，如果省略，则取消之前设置的按键关联

按F5运行便可以启用代码，代码需要放入某个模块中

Sub SetupHotkey()
    Application.OnKey "^q", "QuickSave"  ' Ctrl+Q 触发 QuickSave 宏
End Sub

Sub QuickSave()
    ActiveWorkbook.Save
    MsgBox "文件已保存!", vbInformation
End Sub

禁用某个按键

Sub DisableF1()
    Application.OnKey "{F1}", ""  ' 禁用 F1 键
End Sub

恢复某个按键：

Sub RestoreF1()
    Application.OnKey "{F1}"  ' 恢复 F1 键的默认功能
End Sub

使用多个按键组合

Sub SetupMultipleHotkeys()
    Application.OnKey "^+s", "SpecialSave"  ' Ctrl+Shift+S
    Application.OnKey "%{F2}", "CustomRename"  ' Alt+F2
End Sub

按键符号：

控制键：
^ 表示 Ctrl 键
替代键：
% 表示 Alt 键
Shift 键：
表示 Shift 键
特殊键（用花括号包围）：
{F1} 到 {F15} 表示功能键
{ENTER} 表示回车键
{ESC} 表示 Esc 键
{HOME} 表示 Home 键
{END} 表示 End 键
{LEFT} 表示左箭头键
{RIGHT} 表示右箭头键
{UP} 表示上箭头键
{DOWN} 表示下箭头键
{PGUP} 表示 Page Up 键
{PGDN} 表示 Page Down 键
{TAB} 表示 Tab 键
{BACKSPACE} 或 {BS} 表示退格键
{DELETE} 或 {DEL} 表示删除键
{INSERT} 或 {INS} 表示插入键
{CAPSLOCK} 表示大写锁定键
{NUMLOCK} 表示数字锁定键
{SCROLLLOCK} 表示滚动锁定键
{BREAK} 表示 Break 键
{PRINTSCREEN} 表示 Print Screen 键
组合键示例：
^c 表示 Ctrl+C
%F 表示 Alt+F
+{F1} 表示 Shift+F1
^%{F5} 表示 Ctrl+Alt+F5
Application的OnTime方法

OnTime方法告诉Excel，当到指定的事件时自动运行程序（可以是指定的某个事件，也可以是指定的一段时间之后）

语法：

Application.OnTime EarliestTime, Procedure, [LatestTime], [Schedule]

EarliestTime：指定过程运行的时间（日期和时间）

Procedure：要运行的过程名称（字符串）

LasteTime（可选）：如果错过了EarliestTime，过程最晚应该运行的时间

Schedule（可选）：布尔值，如果为False，则取消先前安排的过程

Sub ScheduleReport()
    Application.OnTime TimeValue("7:37:00"), "GenerateDailyReport"
End Sub

Sub GenerateDailyReport()
    ' 生成报告的代码
    MsgBox "日报已生成！", vbInformation
End Sub

1 thought on “4.Excel事件”

  1. Pingback： 别怕，Excel VBA其实很简单 · 索引 – 李灵杰

发表回复