Search code examples
excelvbaexcel-tables

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

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?

Solution

  • 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