Create log history for a cell value change in a column

Question

I create a log history worksheet and save the change detail of other worksheets.

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

That works for a single cell, e.g.:

If A1 stores "ABC" and changes to "123", Log Detail will save the Cell address, old value, new value, username and date/time.

The big problem is when I select a whole column, such as all of column(B). It will get the error

"type not match".

I know the problem is

oldValue = Target.Value

How can I save the change of a column?

What do you want to happen? I don't really understand "that it also can save the change of column". Do you want to log a selection change? A modification of multiple cells is not easily done by the user, except maybe for Copy+Pasting. The issue with `oldValue = Target.Value` is that when multiple cells are selected, `Target.Value` will return an array, which you can't store in a simple string variable. Thus the type mismatch. So you need to decide what exactly you want to happen in case of a multi-cell selection? Maybe force the selection to only the first cell? — Inarion, Nov 21 '18 at 10:31
Also think of the implications of logging multiple changes at the same time: Should a change in all cells of a column (so 1048576 changes) all be logged on their own? Your log sheet will be instantly filled up. Or do you want to somehow compress these changes into fewer lines? (How so?) — Inarion, Nov 21 '18 at 10:33
sorry for late response, the thing i want to do is, for example, if i change in range("A1") formula: =C1+D1 and then selection a range such as A1-A20 and then Ctrl+D to replace the formula to whole selection. In my mind the record should show 2 record. first one is i replace the formula in range A1 second is i replace the formula in range A2-A20 but i dun know how to display it.... — Nicawong9147, Nov 22 '18 at 01:25
the first i do this vba is i want the change in all cells of a column would be logged on their own, i know that's will be fill up instantly. but my workbook file sometimes got more 2000 record. that would be a great and long process time to do it, so if possbile, 2 record will be show in it. — Nicawong9147, Nov 22 '18 at 01:35
there is another problem is, when i change the formula of a cell and logged it. the logdetail only show the change of value, not change of formula.(i hope it show =A1+B1 in target . value, something like that). can it be change the display format? — Nicawong9147, Nov 22 '18 at 01:38
If you tell a `Range` object to give you its `.Value` it will give you just that: A value. If you want a formula instead, you need to tell it: `Range().Formula`. — Inarion, Nov 22 '18 at 08:35
thanks for your opinion@Inarion Now i change to 'Target.Formula' and replace the code answer you provide. i know that it affects me when i select a range, it will force me to choose the first cell i select. but in other word, some time i need to select a range to make some changes such as copy and paste. Can it be fix even if i select a range, i still can work the function? — Nicawong9147, Nov 23 '18 at 01:42
@Inarion I have updated the vba code, Now it can select by a range. and also if i replace the formula in range A2-A20, it would show me the record, although that's not too clear for people to understand. but there are 3 problem generate, the first one, the undo function of excel cannot be used, second one, the formula change show in log sheet cannot not show properly, it will show 0 or #Value!, the third one, copy and paste a range only show the first cell changes, is it cannot be fix? — Nicawong9147, Nov 23 '18 at 02:54
I've tried addressing your questions in [my answer](https://stackoverflow.com/a/53410365/7778672). See if that is something you can follow. — Inarion, Nov 23 '18 at 16:26

Inarion · Answer 1 · 2018-11-23T16:23:47.157

I'm not entirely sure if that solves your problem, but try the following modification to your Workbook_SheetSelectionChange procedure:

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

Every time the user selects more than one cell the event procedure will change that selection (which raises another change event, with a single-cell target this time) and exit without doing anything else. The criterion for when this selection change should happen can of course be refined to allow for a more specific behavior.

This should make it much harder for the average user to intentionally or accidentally modify more than one cell at a time.

To address questions from your comments:

the undo function of excel cannot be used

This is true. Excel doesn't know how to reverse the actions your code has taken. You need to build this functionality yourself. See this question + accepted answer.

the formula change show in log sheet cannot not show properly, it will show 0 or #Value!

Yes, that's by design. With the line

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

you tell Excel to set that cell's value to a formula. Which Excel then automatically tries to evaluate. (Leading to the errors you're experiencing)
Try the following:

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

This will force Excel to treat the cell value as text and thus it will simply show the formula without evaluating it.

copy and paste a range only show the first cell changes, is it cannot be fix?

This is due to oldValues being an array while you only ever access its first value. See my implementation:

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

This has the weakness that when a user copies multiple cells and then selects a single cell and pastes, it will error out, due to mismatching indices. (It works when you copy, e.g. 3 cells in a row, then select 3 other cells in a row and paste.) Not sure how to avoid that. We'd need to capture the size of the pasted range to update oldValues accordingly. As Excel doesn't expose a Workbook_SheetBeforePaste event, that seems rather tricky.

Sorry for late response @Inarion, I have assignment these 2 days, really sorry for late. i have tested your code, but when i input something in a black cell, it shows me the error. "type mismatch", what's wrong with it? — Nicawong9147, Nov 26 '18 at 03:51
@Nicawong9147 I'm not sure what a cell's color has to do with any of this? Can you make sure there's no other property of that cell that could be the culprit? — Inarion, Nov 27 '18 at 16:39
Sorry, Wrong typing, should be blank cell, not black cell.i put your code in the Thisworkbook and build the LogDetails sheet. No matter what i am input in Sheet1, Sheet2 or Sheet3, just show me the type mismatch. — Nicawong9147, Nov 29 '18 at 01:19
The error code show in this code: LogRange(2).Value = "'" & oldValues(idxRows, idxCols) — Nicawong9147, Nov 29 '18 at 01:22

Create log history for a cell value change in a column

1 Answers1