[Excel VBA] How to Automatically Record Save History | Log Who Saved When

When sharing Excel files, issues often arise where it is unclear “who saved the file and when” or “who is responsible for an entry error.”

To prevent such troubles, it is recommended to implement a system that automatically records the save history.

In this article, I will introduce a VBA macro that utilizes the Workbook_BeforeSave event to log the save time and the user name.

Features

Using this macro enables the following automatic logging functions:

• A record is automatically added the moment Excel is saved.
• The person who saved (Office User Name) is recorded.
• Records are accumulated chronologically in a specified sheet named “SaveHistory”.

VBA Code (Write in ThisWorkbook)

Write the following code directly into the ThisWorkbook object in the VBA Editor (Alt + F11).

Prerequisite: Create a sheet named “SaveHistory” (or change the name in the code) beforehand to store the logs.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim saveTime As String
    Dim userName As String

    ' Specify the sheet for recording history
    ' Ensure a sheet named "SaveHistory" exists
    Set ws = ThisWorkbook.Worksheets("SaveHistory")

    ' Get the first empty row (Last row of Column A + 1)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ' Get current time and user name
    saveTime = Now
    userName = Application.UserName

    ' Write to the history sheet
    With ws
        .Cells(lastRow, 1).Value = saveTime    ' Save Date & Time
        .Cells(lastRow, 2).Value = userName    ' User Name
    End With
End Sub

Structure of the Save History Sheet

As shown above, you can verify who saved the file and when in a list format.

Security and Tamper Prevention

Since the log is written to a standard worksheet, it remains editable by anyone. To prevent unauthorized alteration, consider the following measures:

• Hide the “SaveHistory” sheet.
• Protect the sheet (with a password) so cells cannot be edited manually.
• Lock the VBA Project with a password so the code cannot be viewed or disabled.

Important Note: When Logs Are Not Recorded

This code runs in conjunction with the “Save Event.” Therefore, history will not be recorded in the following cases:

• The file is closed without saving.
• Only AutoRecovery runs (without a manual save).

It is recommended to operate this alongside internal rules that encourage frequent saving.

Summary

To “visualize” entry errors and troubles in Excel, automatically recording the save history is an effective measure.

• Automatically record save time and user name using the Workbook_BeforeSave event.
• Logs can be checked later by saving them to a “SaveHistory” sheet.
• Prevent log tampering by using sheet protection.

This is a simple yet powerful macro that can be applied to periodic audits and task management.