vba 显示单元格的旧值和新值

Display old and new values for a cell

提问by imageicb

I have a worksheet that logs changes that uses have made to cells. It goes as follows

我有一个工作表，用于记录对单元格所做的更改。它如下

Public OldVal As String
Public NewVal As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
OldVal = Target.Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim LDate As String

If Target.Cells.Count > 1 Then Exit Sub
NewVal = Target.Value
Sheets("corrections").Cells(Rows.Count, "A").End(xlUp)(2).Value = Now & "_Sheet " & ActiveSheet.Name & " Cell " & Target.Address(0, 0) & " was changed from '" & OldVal & "' to '" & NewVal & "'"
OldVal = ""
NewVal = ""
End Sub

The problem im having is that for some reason it will never display the previous value. it will output it only as Sheet FA Cell B5 was changed from '' to '12' even if say for example 10 was in the cell prviously.

我遇到的问题是由于某种原因它永远不会显示以前的值。它只会在 Sheet FA Cell B5 从 '' 更改为 '12' 时输出它，即使例如 10 之前在单元格中也是如此。

I also was curious to know is there a way that you can have it so that this code is not running at all times. Id prefer to have a button you click and at that point it will initiate and start logging changes.

我也很想知道有没有一种方法可以让这段代码不一直运行。我更喜欢点击一个按钮，然后它会启动并开始记录更改。

Thanks

谢谢

采纳答案by imageicb

Thr problem why my OldVal was not showing up was that it was being held in array. So when I told it to look at OldVal(1, 1) it works just as it should. Thanks for the help. The final working code is:

为什么我的 OldVal 没有出现的问题是它被保存在数组中。因此，当我告诉它查看 OldVal(1, 1) 时，它会正常工作。谢谢您的帮助。最终的工作代码是：

Public OldVal As String Public NewVal As String

公共旧值作为字符串公共新值作为字符串

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Sheets("corrections").Range("G1") <> "Yes" Then Exit Sub OldVal = Target.Value2

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Sheets("corrections").Range("G1") <> "Yes" Then Exit Sub OldVal = Target.Value2

End Sub

结束子

Private Sub Worksheet_Change(ByVal Target As Range) If Sheets("corrections").Range("G1") <> "Yes" Then Exit Sub If Target.Cells.CountLarge > 1 Then Exit Sub NewVal = Target.Value Sheets("corrections").Cells(Rows.Count, "A").End(xlUp)(2).Value = Now & "_Sheet " & ActiveSheet.Name & " Cell " & Target.Address(0, 0) & " was changed from '" & OldVal(1, 1) & "' to '" & NewVal & "'"

Private Sub Worksheet_Change(ByVal Target As Range) If Sheets("corrections").Range("G1") <> "Yes" Then Exit Sub If Target.Cells.CountLarge > 1 Then Exit Sub NewVal = Target.Value Sheets("更正").Cells(Rows.Count, "A").End(xlUp)(2).Value = Now & "_Sheet " & ActiveSheet.Name & " Cell " & Target.Address(0, 0) & " 是从 '" & OldVal(1, 1) & "' 改为 '" & NewVal & "'"

OldVal = ""
NewVal = ""

End Sub

结束子

回答by Gary's Student

I got your posted code to work with a very small change:

我让您发布的代码进行了很小的更改：

Public OldVal As String
Public NewVal As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    OldVal = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LDate As String
    If Target.Cells.Count > 1 Then Exit Sub
    NewVal = Target.Value
    Application.EnableEvents = False
        Sheets("corrections").Cells(Rows.Count, "A").End(xlUp)(2).Value = Now & "_Sheet " & ActiveSheet.Name & " Cell " & Target.Address(0, 0) & " was changed from '" & OldVal & "' to '" & NewVal & "'"
    Application.EnableEvents = True
    OldVal = ""
    NewVal = ""
End Sub

For your second question, start with:

对于你的第二个问题，从：

Application.EnableEvents = False

Hook your button onto a macro like this:

将您的按钮挂在这样的宏上：

Sub StartLogging()
    Application.EnableEvents = True
End Sub

回答by Takedasama

Your code is working fine for me. As for the enable/disable macro, you just need to insert this line before (/above) each IF (in both of your macros). Optional check for a more appropriate cell to store the Yes/No option (rather than "X1"):

你的代码对我来说很好。至于启用/禁用宏，您只需要在每个 IF 之前（/上方）（在您的两个宏中）插入此行。可选检查更合适的单元格来存储是/否选项（而不是“X1”）：

If Sheets("corrections").Range("X1") <> "Yes" Then Exit Sub
' where you can change X1 for a more appropriate cell

To create the buttons just add the shapes/objects and assign the macros below:

要创建按钮，只需添加形状/对象并分配以下宏：

Sub Enable_Logs()
Sheets("corrections").Range("X1").Value = "Yes"
End Sub
Sub Disable_Logs()
Sheets("corrections").Range("X1").Value = "No"
End Sub

Note!To add buttons with macro assigned to them: press Alt + N, +SH and select a shape. Then, right click on the shape > Assign Macro (and select the corresponding macros). Note!for the 1st time you run the macro, you should manually set the "Yes" value in cell X1.

笔记！要添加分配有宏的按钮：按 Alt + N、+SH 并选择一个形状。然后，右键单击形状 > 分配宏（并选择相应的宏）。笔记！第一次运行宏时，您应该在单元格 X1 中手动设置“是”值。