时间戳

首页 > 解决方案 > 为列中的单元格值更改创建日志历史记录

excel - 为列中的单元格值更改创建日志历史记录

问题描述

我创建了一个日志历史工作表并保存其他工作表的更改详细信息。

Dim oldValue As Variant

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = "Data"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now

Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Formula
End Sub

这适用于单个单元格，例如：

如果 A1 存储“ABC”并更改为“123”，Log Detail 将保存 Cell 地址、旧值、新值、用户名和日期/时间。

最大的问题是当我选择一整列时，例如所有列（B）。它会得到错误

“类型不匹配”。

我知道问题是

oldValue = Target.Value

如何保存列的更改？

标签： excelvba

解决方案

我不完全确定这是否能解决您的问题，但请尝试对您的Workbook_SheetSelectionChange程序进行以下修改：

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then
        Target(1).Select
        Exit Sub
    End If
    oldValue = Target.Value
    oldAddress = Target.Address
End Sub

每次用户选择多个单元格时，事件过程都会更改该选择（这会引发另一个更改事件，这次是单个单元格目标）并退出而不做任何其他事情。当然，可以细化何时应该发生这种选择变化的标准，以允许更具体的行为。

这应该会使普通用户一次有意或无意地修改多个单元格变得更加困难。

要解决您评论中的问题：

excel的undo功能无法使用

这是真实的。Excel 不知道如何撤销您的代码已执行的操作。您需要自己构建此功能。看到这个问题+接受的答案。

日志表中显示的公式更改无法正确显示，它将显示0或#Value!

是的，这是设计使然。随着线

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula

您告诉 Excel 将该单元格的值设置为公式。然后 Excel 会自动尝试评估。（导致您遇到的错误）
尝试以下操作：

' Prepend the formula with an apostrophe
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = "'" & Target.Formula

这将强制 Excel 将单元格值视为文本，因此它将仅显示公式而不对其进行评估。

复制和粘贴一个范围只显示第一个单元格更改，它不能修复吗？

这是因为它oldValues是一个数组，而您只能访问它的第一个值。查看我的实现：

Option Explicit

Dim oldValues As Variant

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Const LogSheet As String = "LogDetails"

    If Sh.Name = LogSheet Then Exit Sub

    Application.EnableEvents = False

    With Worksheets(LogSheet)

        Dim idxRows As Long
        For idxRows = 1 To Target.Rows.Count

            Dim idxCols As Long
            For idxCols = 1 To Target.Columns.Count

                Dim ChangedCell As Range
                Set ChangedCell = Target.Rows(idxRows).Columns(idxCols)

                Dim LogRow As Long
                LogRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
                Dim LogRange As Range
                Set LogRange = .Range(.Cells(LogRow, 1), .Cells(LogRow, 5))

                LogRange(1).Value = Sh.Name & "!" & ChangedCell.Address(False, False)
                LogRange(2).Value = "'" & oldValues(idxRows, idxCols) ' error here when pasting a range of different size than has been selected before pasting
                LogRange(3).Value = ChangedCell.Formula
                LogRange(4).Value = Environ("username")
                LogRange(5).Value = Now

            Next idxCols

        Next idxRows

        .Columns("A:E").AutoFit

    End With

    Application.EnableEvents = True

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    oldValues = Target.Formula
End Sub

这有一个弱点，当用户复制多个单元格然后选择一个单元格并粘贴时，由于索引不匹配，它会出错。（当您复制时有效，例如连续复制 3 个单元格，然后连续选择 3 个其他单元格并粘贴。）不知道如何避免这种情况。我们需要捕获粘贴范围的大小以进行oldValues相应更新。由于 Excel 不公开Workbook_SheetBeforePaste事件，这似乎相当棘手。

推荐阅读