Display old and new values for a cell

Question

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.

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

If you are using xl2007+ then `Target.Cells.Count` can give you error. Use `Target.Cells.CountLarge` instead. — Siddharth Rout, Oct 04 '13 at 13:48

score 2 · Answer 1 · answered Oct 04 '13 at 13:35

2

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

answered Oct 04 '13 at 13:35

Gary's Student

95,722
10
59
99

`Application.EnableEvents = True` before the `End Sub` :) – David Zemens Oct 04 '13 at 13:40
2

@DavidZemens: That won't help if there is an error. You should always use error handling when you are using `Worksheet_Change` or handling events. [SEE THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) – Siddharth Rout Oct 04 '13 at 13:50
@SiddharthRout +1 your suggestion *does* re-enable Events, it just adds error handling, too. This is a good thing, but fundamentally he needs to reenable events, otherwise he will have to run the `StartLogging` macro after every change event. – David Zemens Oct 04 '13 at 14:35

score 0 · Answer 2 · answered Oct 04 '13 at 13:48

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 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.

Thanks both of these worked like a charm. I really appreciate it. — imageicb, Oct 08 '13 at 17:19

score 0 · Accepted Answer · answered Oct 09 '13 at 17:59

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:

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

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

OldVal = ""
NewVal = ""

End Sub

Display old and new values for a cell

3 Answers3

Linked