利用Excel VBA代码编写日志,查看表格操作修改记录。
在编辑excel表格时,如果能时时记录我们的操作过程,在需要查看修改过程时就很方便了。
以下是如何将代码添加到当前工作簿的步骤:
1. 打开Excel,并打开您的工作簿。
2. 按下"ALT + F11"打开VBA编辑器。
3. 在VBA编辑器中,找到您的工作簿项目(通常命名为VBAProject - 你的工作簿名称)。
4. 点击插入模块。
5. 在"这个工作簿"的代码窗口中,粘贴VBA代码。
6. 关闭VBA编辑器。
现在,您在修改相关工作表的单元格内容时,会自动记录日志在名为"日志"的工作表中。
Option Explicit
'在所有过程之前用Dim语句定义的变量r1是模块级变量,应模块中所有的过程都可以使用它
Dim r1 '定义一个模块级变量,用户保存单元格的数据
'第一个事件过程,用于记录被更改前单元格中保存的数据
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub '选中多个单元格时退出程序
If Target.Formula = "" Then '根据选中单元格中保存的数据,确定给变量r1赋什么值
r1 = "空"
Else
r1 = Target.Text
End If
End Sub
'第二个事件过程,用于批注记录单元格修改前后的信息
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
Dim r2 '定义变量保存单元格修改后的内容
If Target.Formula = "" Then '判断单元格是否被修改为空单元格
r2 = "空"
Else
r2 = Target.Formula
End If
If r1 = r2 Then Exit Sub '如果单元格修改前后的内容一样则退出程序
Dim r3 '定义一个批注变量
Dim r4 '定义一个变量保存批注内容
Set r3 = Target.Comment '将被修改单元格的批注赋给变量r3
If r3 Is Nothing Then Target.AddComment '如果单元格中没有批注则新建批注
r4 = Target.Comment.Text '将批注的内容保存到变量r4中
'重新修改批注的内容=原批注内容+当前日期和时间+原内容+修改后的新内容
Target.Comment.Text Text:=r4 & Chr(10) & Format(Now(), "yyyy-mm-dd hh:mm") & Chr(32) & "原内容:" & r1 & Chr(32) & "修改为:" & r2
'根据批注内容自动调整批注大小
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub
但这个代码有BUG,就是粘贴复制修改时,需要选择性粘贴,只粘贴值,否则历史修改记录会被覆盖。
在上述基础上,
增加了修改时询问修改原因,并体现在批注里,适合小批量修改,大批量修改会出现弹窗反复问修改原因。
Option Explicit
'在所有过程之前用Dim语句定义的变量r1是模块级变量,应模块中所有的过程都可以使用它
Dim r1 '定义一个模块级变量,用户保存单元格的数据
'第一个事件过程,用于记录被更改前单元格中保存的数据
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub '选中多个单元格时退出程序
If Target.Formula = "" Then '根据选中单元格中保存的数据,确定给变量r1赋什么值
r1 = "空"
Else
r1 = Target.Text
End If
End Sub
'第二个事件过程,用于批注记录单元格修改前后的信息
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
Dim r2 '定义变量保存单元格修改后的内容
If Target.Formula = "" Then '判断单元格是否被修改为空单元格
r2 = "空"
Else
r2 = Target.Formula
End If
If r1 = r2 Then Exit Sub '如果单元格修改前后的内容一样则退出程序
Dim r3 '定义一个批注变量
Dim r4 '定义一个变量保存批注内容
Set r3 = Target.Comment '将被修改单元格的批注赋给变量r3
If r3 Is Nothing Then Target.AddComment '如果单元格中没有批注则新建批注
Dim reason
reason = InputBox("请输入修改原因:", "修改原因")
r4 = Target.Comment.Text '将批注的内容保存到变量r4中
'重新修改批注的内容=原批注内容+当前日期和时间+原内容+修改后的新内容
Target.Comment.Text Text:=r4 & Chr(10) & Format(Now(), "yyyy-mm-dd hh:mm") & Chr(32) & "原内容:" & r1 & Chr(32) & "修改为:" & r2 & "修改原因: " & reason
'根据批注内容自动调整批注大小
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub