以下为《Excel VBA——将数据修改过程添加至批注中记录》原文内容,应读者需求,只在Worksheet_Change的代码结尾处增加了定义批注框内字体格式的代码行。采访一下,无论是工作中还是生活中,大家是不是都有想吃把后悔药的时候?比如修改数据一顿操作猛如虎,结果发现脑残都给改错了,想恢复到之前某步的数据,奈何脑子不好使记不住啊,怎么办?
本文给出的方法是:采用VBA中事件驱动的方法,将数据修改过程详细记录至单元格批注中,并在关闭工作簿时,自动删除所有批注另存一个clean版的数据表。了解什么是VBA的事件?请看本号《Excel VBA——动态显示图表》。分解一下,此方法涉及三个事件过程,包括两个工作表事件和一个工作簿事件。
工作表事件
Worksheet_SelectionChange(只要选择了就记录)
将事件过程写入对应工作表的代码窗口内。 在修改单元格内容前首先要选中单元格,这里利用工作表的SelectionChange事件,自动记录修改前单元格中的数据。代码分享
Option ExplicitDim rngvalue$ '定义模块级变量,其他事件过程也可使用Dim revvalue$Dim comstr$Dim rngcom As CommentPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) '记录修改前单元格中的数据 If Target.Cells.Count > 1 Then '选中多个单元格时退出程序 Exit Sub End If If Target.Value = "" Then '如选中的是空单元格,记录为“空” rngvalue = "空" Else rngvalue = Target.Value '如选中的是非空单元格,将该单元格中的数据存入字符串变量rngvalue End IfEnd Sub
Worksheet_Change(只要修改了就加注)当单元格中数据被修改后,就为该单元格增加一个批注,并在批注里写明修改的时间和修改前后的内容。
代码分享
Private Sub Worksheet_Change(ByVal Target As Range) '为变动单元格增加批注,写明修改时间和具体修改内容 If Target.Cells.Count > 1 Then '修改多个单元格时退出程序 Exit Sub End If If Target.Value = "" Then '如删除单元格内容,记录为“空” revvalue = "空" Else revvalue = Target.Value '将单元格修改后的数据存入字符串变量revvalue End If If rngvalue = revvalue Then '如果修改前后的数据一样时退出程序 Exit Sub End If Set rngcom = Target.Comment If rngcom Is Nothing Then Target.AddComment '被修改的单元格如果没有设批注就新增一个批注 End If comstr = Target.Comment.Text '如果对单元格多次修改,则依次写明每次的修改记录 Target.Comment.Text Text:=comstr & Chr(10) & Format(Now(), "yyyy-mm-dd hh:mm:ss") _ & Chr(10) & "将 " & rngvalue & " 修改为 " & revvalue '在批注里写明修改时间,以及“将……修改为……” With Target.Comment.Shape.TextFrame .Characters.Font.Name = "等线" '字体为等线 .Characters.Font.Size = 12 '字号为12 .Characters.Font.ColorIndex = 3 '字体为红色 .AutoSize = True End WithEnd Sub
工作簿事件
Workbook_BeforeClose如果对多个单元格发生过多次修改,通过上面两个工作表事件的触发会产生很多的批注记录框。实际使用时可能需要输出一个删除掉所有批注的clean的数据表。这个过程需要在关闭工作簿时触发,也就是使用工作簿事件过程,需要写在ThisWorkbook的代码窗口中。
代码分享
Option ExplicitPrivate Sub Workbook_BeforeClose(Cancel As Boolean) '关闭工作簿事件 Application.DisplayAlerts = False If Cancel = False Then ThisWorkbook.Save '保存带批注框的工作簿 Cells.ClearComments '删除所有批注 ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\test_clear", FileFormat:=xlOpenXMLWorkbook '将删除批注的工作表另存为不带宏的xlsx表 End If Application.DisplayAlerts = TrueEnd Sub
运行效果
图1 批注记录修改过程
汇总
Workbook事件列表
表1 常用Workbook事件 Worksheet事件列表 表2 常用Worksheet事件 另存文件格式列表表3 常用文件格式 总结本文分享了基于VBA的事件过程将对单元格数据的修改过程记录在批注中的方法。事件过程就是给操作添加一个个的触发条件,一旦做了某件事,就自动触发另外一件事,这样可以避免人工运行程序的操作。但是,事件过程也必须写在相应的工作簿、工作表对象模块中才能自动执行,而写在新插入的模块中则无法自动运行。