显示单元格的旧值和新值 [英] Display old and new values for a cell

问题描述

我有一个工作表，用于记录对单元格使用的更改.如下

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

我遇到的问题是由于某种原因它永远不会显示以前的值.仅当Sheet FA单元格B5从"更改为"12"时，才输出它，即使例如以前在该单元格中有例如10.

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.

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

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.

谢谢

推荐答案

我的OldVal没出现的原因是它被放置在数组中.因此，当我告诉它查看OldVal(1，1)时，它可以正常工作.谢谢您的帮助.最终的工作代码是:

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作为字符串将公共NewVal作为字符串

Public OldVal As String Public NewVal As String

Private Sub Worksheet_SelectionChange(ByVal目标作为范围)如果Sheets("corrections").Range("G1")<>"Yes"，则退出SubOldVal = Target.Value2

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

结束子

私人子工作表_更改(按目标的ByVal目标)如果Sheets("corrections").Range("G1")<>"Yes"，则退出Sub如果Target.Cells.CountLarge> 1然后退出SubNewVal = Target.ValueSheets("corrections").Cells(Rows.Count，"A").End(xlUp)(2).Value = Now&"_Sheet"&ActiveSheet.Name&单元格"和Target.Address(0，0)&已从"和;"更改为"OldVal(1，1)&'至'"&新Val&'"

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 & "'"

OldVal = ""
NewVal = ""

结束子

这篇关于显示单元格的旧值和新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！