How to Track Who Changed What in Excel and Access

Tracking who changed what is how you settle arguments about pricing edits, status flips, and “I never touched that record.” This tutorial shows practical audit logging patterns in Microsoft Access and Excel so you keep a usable history of inserts, updates, and deletes.

At minimum, each log row should answer:

Create a table to store audit log entries:

Press Alt+F11, insert a new module, and add a function that writes one audit row. Example shape:

Function WriteAuditLog(TableName As String, RecordID As String, FieldName As String, OldValue As Variant, NewValue As Variant, ActionType As String)

Dim db As DAO.Database

Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("tblAuditLog", dbOpenDynaset)

rs.AddNew

rs!TableName = TableName

rs!RecordID = RecordID

rs!FieldName = FieldName

rs!OldValue = OldValue

rs!NewValue = NewValue

rs!UserName = Environ("USERNAME")

rs!ChangeDate = Now()

rs!ActionType = ActionType

rs.Update

rs.Close

Set rs = Nothing

Set db = Nothing

End Function

Prefer form events over table datasheet edits. Open the form in Design View and wire BeforeUpdate, BeforeInsert, and delete confirm events. Production Access forms are the right place to enforce logging because you control how people change data.

Example BeforeUpdate sketch:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then

If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

WriteAuditLog Me.RecordSource, Me!ID, ctl.Name, ctl.OldValue, ctl.Value, "UPDATE"

End If

End If

Next ctl

End Sub

Press Alt+F11, insert a module, and append one row per change:

Sub WriteExcelAuditLog(SheetName As String, CellAddress As String, OldValue As Variant, NewValue As Variant)

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("AuditLog")

Dim LastRow As Long

LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

ws.Cells(LastRow, 1) = Now()

ws.Cells(LastRow, 2) = Environ("USERNAME")

ws.Cells(LastRow, 3) = SheetName

ws.Cells(LastRow, 4) = CellAddress

ws.Cells(LastRow, 5) = OldValue

ws.Cells(LastRow, 6) = NewValue

ws.Cells(LastRow, 7) = "UPDATE"

End Sub

Right-click the data sheet tab → View Code. Capture old values carefully: naive Undo patterns can recurse or fail on multi-cell edits. Start with single-cell ranges, disable events while you write the log, and re-enable them in an error handler.

Environ("USERNAME") is a start. Shared Windows accounts and unlocked sessions blur history. For stronger accountability, store an application login or network identity in a session variable and write that value into every audit row.

Log the record key in a delete confirm handler before the row is gone. Capture enough field context that a reviewer can understand what was removed.

Shared network workbooks, frequent multi-cell pastes, and people who disable macros will defeat sheet-based logs. Move critical data to Access or SQL Server with form-based entry, or ask an Excel consultant to redesign the control surface instead of bolting more VBA onto a fragile file.

Confirm macros are enabled, event procedure names match Access/Excel expectations, the audit table or sheet exists, and WriteAuditLog runs when called alone from the Immediate window.

Index UserName and ChangeDate in Access, archive old rows, and avoid logging every keystroke-level noise. Log field saves, not intermediate typing.

They edited tables directly, broke VBA protection, or opened a copy without macros. Close direct table access, protect VBA, and treat macro-free copies as out of process.

Excel Access Expert implements fixed-scope audit trails in Access and Excel for teams that need clear accountability—not generic security theater. For Access event-driven logging, see Access VBA macros development, or start with an MS Excel consultant review when the workbook already runs the business and a DIY log would be easy to disable.