如何修改VBA代码实现多单元格变更日志分栏存储及LOG表加密

核心修改说明 #

• 日志分栏存储：取消原有的字符串拼接逻辑，定位到LOG表下一条空行后，分别向A-E列写入对应的字段值
• LOG表权限控制：通过设置工作表保护的UserInterfaceOnly参数为True，实现VBA可正常写入、普通用户无法编辑；可进一步设置工作表为超级隐藏状态，实现普通用户无法查看LOG表内容

完整修改后代码 #

监控修改的工作表代码模块 #

以下代码需要放到你要监控修改操作的普通工作表的代码模块中（非LOG表）：

' 模块顶部通用声明
Dim RangeValues As Variant
Dim lCols As Long, lRows As Long

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UN As String: UN = Application.UserName
    Dim nextRow As Long
    ' 定位LOG表下一条空行
    nextRow = Sheets("LOG").Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    If Target.Cells.Count = 1 Then
        If Target.Value <> RangeValues Then
            ' 分栏写入各字段
            Sheets("LOG").Cells(nextRow, 1) = Now ' A列：日期时间
            Sheets("LOG").Cells(nextRow, 2) = UN ' B列：操作用户
            Sheets("LOG").Cells(nextRow, 3) = Target.Address ' C列：单元格地址
            Sheets("LOG").Cells(nextRow, 4) = RangeValues ' D列：原值
            Sheets("LOG").Cells(nextRow, 5) = Target.Value ' E列：新值
        End If
        Exit Sub
    End If
    
    ' 多单元格修改场景处理
    Dim r As Long, c As Long
    For r = 1 To lRows
        For c = 1 To lCols
            If Target.Cells(r, c).Value <> RangeValues(r, c) Then
                nextRow = Sheets("LOG").Cells(Rows.Count, 1).End(xlUp).Row + 1
                Sheets("LOG").Cells(nextRow, 1) = Now
                Sheets("LOG").Cells(nextRow, 2) = UN
                Sheets("LOG").Cells(nextRow, 3) = Target.Cells(r, c).Address
                Sheets("LOG").Cells(nextRow, 4) = RangeValues(r, c)
                Sheets("LOG").Cells(nextRow, 5) = Target.Cells(r, c).Value
            End If
        Next c
    Next r
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    RangeValues = Target.Value
    lCols = Target.Columns.Count
    lRows = Target.Rows.Count
End Sub

ThisWorkbook代码模块 #

以下代码放到工作簿的ThisWorkbook模块中，实现打开工作簿自动配置LOG表权限：

Private Sub Workbook_Open()
    Const PWD As String = "你的自定义密码" ' 替换为你自己的密码
    Dim logSht As Worksheet
    
    ' 自动创建LOG表（如果不存在）
    On Error Resume Next
    Set logSht = Sheets("LOG")
    On Error GoTo 0
    If logSht Is Nothing Then
        Set logSht = ThisWorkbook.Sheets.Add
        logSht.Name = "LOG"
        ' 写入表头
        logSht.Range("A1:E1") = Array("操作时间", "操作用户", "修改单元格", "修改前值", "修改后值")
    End If
    
    ' 设置保护：仅允许VBA修改，用户无法编辑
    logSht.Protect Password:=PWD, UserInterfaceOnly:=True
    ' 设为超级隐藏，普通用户无法查看，不需要的话可以注释掉下面这行
    logSht.Visible = xlSheetVeryHidden
End Sub

使用注意事项 #

• 请将代码中的你的自定义密码替换为你自己设置的密码，后续需要查看LOG表时，可在VBA编辑器中修改logSht.Visible = xlSheetVisible并输入密码即可解锁查看
• 代码兼容单/多单元格修改场景，会自动记录所有变更内容
• 首次打开启用宏后会自动创建LOG表，无需手动提前新建

内容的提问来源于stack exchange，提问作者user16269017