How to check if Excel table cell has been edited by user?

Question

What are options to monitor changes in an Excel table?

Possible solution I can think of is to have a clone copy of the table, say in a hidden worksheet and a formula which compares both sheets.

Is there any other way?

Answer 1

Well, there are multiple ways.

On way would be to subscribe to Worksheet_Change event with such method:

Private Sub Worksheet_Change(ByVal Target As Range)
    'some code, which will compare values and store info in a file
End Sub

I suggested also way of logging such event: take user name and what has changed and write this info to a file.

Also, you'd need to do some extra coding to see if this is the change you are interested in, but this is left for you to discover, as it is to broad to describe all the options here :)

Answer 2

I've come up with a code (as an event based code - Worksheet_Change) like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Cells
Dim lastrow As Long
Dim username As String


If Intersect(Target, rg) Is Nothing Then Exit Sub

On Error GoTo ExitHere

Application.EnableEvents = False

With SomeOtherSheet
    lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
    .Range("H" & lastrow + 1) = Now
    .Range("I" & lastrow + 1) = Target.Address
    .Range("J" & lastrow + 1) = Environ("Username")
    .Range("K" & lastrow + 1) = Application.username
End With

ExitHere:
Application.EnableEvents = True
End Sub

It records any change made by a user in the given Sheet (the one where the code is written). It will show me in another Sheet who, when and where the change was done. The only problem I have with this matter is that the user has to enable macros, otherwise it doesn't work... I don't know how to reasonably solve this issue...

Answer 3

I totally agree with @Michał Turczyn. For security reasons is better to keep records about the changes. You could use:

Option Explicit
Dim OldValue As String

Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox "The old value was " & OldValue & "." & vbNewLine & _
            "The new value is " & Target.Value & "." & vbNewLine & _
            "Date of change " & Now & "." & vbNewLine & _
            "Change by " & Environ$("computername") & "."             

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    OldValue = Target.Value

End Sub