[Excel VBA] How to Detect Who Has a File Open (Preventing Shared File Conflicts)

2025年12月30日

Background

When multiple people use an Excel file on a shared server, someone might start editing without knowing that another person already has the file open. This often leads to accidental overwrites or errors.

For example, “Mr. Mori opens the file first, but Mr. Hayashi opens it later and starts editing.”

To prevent this situation, I implemented a method using VBA to explicitly display “who has the file open.”

Specifications

When the file is opened, the current username is automatically recorded in cell A1.
If a name is already recorded in A1 when opening, a warning message saying “[Name] is using this file” is displayed.
When the file is closed, if the current user is the one who was recorded, the content of A1 is automatically cleared.
The username is retrieved using Environ("USERNAME") (Windows login name).

Configuration Example

User 1: “Mori”
User 2: “Hayashi”
Target Cell: Cell A1 on Sheet1

Implementation Code (ThisWorkbook)

Paste the following code into the ThisWorkbook module in the VBA editor.

' Process when opening the file
Private Sub Workbook_Open()
    Dim userName As String
    Dim cellContent As String

    ' Check cell A1 on Sheet1
    cellContent = Sheets("Sheet1").Range("A1").Value

    If cellContent = "" Then
        ' A1 is empty -> Record current username
        userName = Environ("USERNAME")
        Sheets("Sheet1").Range("A1").Value = userName
    Else
        ' Someone's name is in A1 -> Show message
        MsgBox cellContent & " is using this file.", vbExclamation, "File In Use"
    End If
End Sub

' Process when closing the file
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim currentUser As String
    currentUser = Environ("USERNAME")

    ' If I am the user who opened it, clear A1
    If Sheets("Sheet1").Range("A1").Value = currentUser Then
        Sheets("Sheet1").Range("A1").ClearContents
    End If
End Sub

How the Code Works

When Opening:

If A1 is empty, it records your username.
If someone else’s name is already there, it notifies you with a warning message.

When Closing:

It clears cell A1 only if the name written in A1 matches your username.
This prevents you from accidentally clearing the status if someone else opened the file while you were working.

Precautions

Cell A1 is used as a “usage record,” so please do not use it for other purposes.
This system allows you to notice if someone is using the file via a warning, but it does not technically lock the file.
If you need strict simultaneous editing control, consider using Shared Mode or SharePoint integration.

Summary

By using Excel VBA, you can simply visualize who is opening a file.

Checking and controlling access beforehand is important to prevent conflicts in shared files.
By utilizing cell A1 and the login user name, you can implement this system easily without disrupting operations.

If you want to prevent the “I don’t know who has the file open” situation, please try this method.