Excel detecting and keeping track of (value) changes in any worksheet

Question

I've managed to write a code that detects value changes of particular cells in any worksheet, but I've struggled to construct something that detects and keeps track of ranged (value) changes.

For example, if a user decides to copy and paste some range of data (lets say more than 1 cell), it will not get caught by the macro. Same goes for a user selecting a range and then manually entering values into each cell while range is still selected.

My current code is constructed of 2 macros, the first runs anytime a worksheet selection change occurs and it stores the target.value into a previous value variable. The second macro runs anytime a worksheet change occurs and it tests if the targeted value is different than the previous one, if so it then notifies the user of the change that had occurred.

You should edit your post and add your code or what you've tried so far, otherwise you might not get help.... — Kathara, Jan 26 '16 at 16:06
Look at the "related" questions down on the right below your question - lots of previous similar questions here (with answers) — Tim Williams, Jan 26 '16 at 16:58

score 2 · Answer 1 · edited May 23 '17 at 12:07

OK I don't really see anything here which covers the whole thing, so here's a rough attempt.

It will handle single or multi-cell updates (up to some limit you can set beyond which you don't want to go...)

It will not handle multi-area (non-contiguous) range updates, but could be extended to do so.

You likely should add some error handling also.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Where As String, OldValue As Variant, NewValue As Variant
    Dim r As Long, c As Long

    Dim rngTrack As Range

    Application.EnableEvents = False
    Where = Target.Address
    NewValue = Target.Value
    Application.Undo
    OldValue = Target.Value 'get the previous values
    Target.Value = NewValue
    Application.EnableEvents = True

    Set rngTrack = Sheets("Tracking").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

    'multi-cell ranges are different from single-cell ranges
    If Target.Cells.CountLarge > 1 And Target.Cells.CountLarge < 1000 Then
        'multi-cell: treat as arrays
        For r = 1 To UBound(OldValue, 1)
        For c = 1 To UBound(OldValue, 2)
            If OldValue(r, c) <> NewValue(r, c) Then
                rngTrack.Resize(1, 3).Value = _
                  Array(Target.Cells(r, c).Address, OldValue(r, c), NewValue(r, c))
                Set rngTrack = rngTrack.Offset(1, 0)
            End If
        Next c
        Next r
    Else
        'single-cell: not an array
        If OldValue <> NewValue Then
            rngTrack.Resize(1, 3).Value = _
              Array(Target.Cells(r, c).Address, OldValue, NewValue)
            Set rngTrack = rngTrack.Offset(1, 0)
        End If
    End If

End Sub

"Undo" part to get the previous values is from Gary's Student's answer here: Using VBA how do I detect when any value in a worksheet changes?

Thanks for your answer. This is just what I was looking for. Is there any way of making this work on a higher - for example workbook level? Thank you so much. Kind regards, Domen — user3729625, Feb 16 '16 at 13:57
There is a `Workbook_SheetChange` event at the Workbook level. You could use very similar code there: just need to add tracking of the worksheet name, and exclude any sheets you don'r want tracked (such as the tracking sheet itself). — Tim Williams, Feb 16 '16 at 16:52
Is there any way to get past the selection return that happens while application.undo code goes through. Like if I write "5" into a blank cell and then click to another cell, (application.undo) will return my selection to the cell in which I changed the value, so to the cell where I wrote 5. Is there any way to prevent this from happening? I thank you in advance for any wisdom you might share. Kind regards — user3729625, Mar 21 '16 at 22:08
Before executing the undo, store the current selection: you can then restore it after the undo — Tim Williams, Mar 22 '16 at 01:45

score 1 · Answer 2 · answered Jan 26 '16 at 16:37

1

This subs will work for you but you have just implement codes in every sheet manually. Just need to copy paste. See below screenshot which is for 1 sheet Sheet1

(1) Declare a public variable.

Public ChangeTrac As Variant

(2) Write below codes in Worksheet_SelectionChange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ChangeTrac = Target.Value
End Sub

(3) write below codes in Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Cells()) Is Nothing Then
        If ChangeTrac <> Target.Value Then
            MsgBox "Value changed to Sheet1 " & Target.Address & " cell."
            Range(Target.Address).Select
        End If
    End If
End Sub

Then test by changing data in any cell. It will prompt if any cell value is changed.

answered Jan 26 '16 at 16:37

Harun24hr

30,391
4
21
36

It actually returns me an error. (run-time error '13' type mismatch).And the problem that I had was detecting what occurred. What changed to what was my question, so I would have to store the previous value somewhere. It seems that excel has problems with storing values into ranges. – user3729625 Jan 27 '16 at 08:48
Would It help if I copy my code and film myself what to show you what I was searching for? – user3729625 Jan 27 '16 at 10:26
Better you share a sample workbook. Just upload the sample file to google drive and share link here. – Harun24hr Jan 27 '16 at 10:35
https://onedrive.live.com/edit.aspx?cid=47bc981d512cff00&page=view&resid=47BC981D512CFF00!774&parId=47BC981D512CFF00!136&app=Excel As you can see this code keeps track of single cell changes. But if I decide I want to change "an area" or more than one cell at a time (for instance by copy - pasting,...), it doesn't detect and record that. – user3729625 Jan 27 '16 at 10:40
Code is like this: Dim PreviousValue Public Sub Worksheet_Change(ByVal Target As Range) logDate = Format(Now(), "dd/mmm/yyyy") logTime = Format(Now(), " hh: mm: ss") On Error Resume Next If ActiveSheet.UsedRange.Address = "$A$1" And Range("A1") = "" Then Else If Target.Value <> PreviousValue Then Sheets("Audit").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _ logDate & " " & logTime & ": " & Environ("Username") & " changed cell " & Target.Address & _ " from " & PreviousValue & " to " & Target.Value & " in " & ActiveSheet.Name End If End If End Sub – user3729625 Jan 27 '16 at 10:44
Public Sub Worksheet_SelectionChange(ByVal Target As Range) PreviousValue = Target.Value End Sub – user3729625 Jan 27 '16 at 10:44
It's much more complex to track multi-cell changes - that's why I suggested you review the previous times this has been asked here. – Tim Williams Jan 27 '16 at 22:47
Mind linking me any such similar themes, I don't find any using "track multi-cell changes in excel stackoverflow". – user3729625 Jan 28 '16 at 10:42

Excel detecting and keeping track of (value) changes in any worksheet

2 Answers2

Linked