Cat On A Spreadsheet

Building Multi-User UserForms: Handling Conflicts and Adding Record Locking

Once you’ve built a CRUD-style UserForm in Excel, the next natural step is sharing it with others. But when multiple users access the same workbook at the same time, conflicts happen:

Two people editing the same record simultaneously
Accidental overwrites when someone saves after another user
Deleted records still being referenced

To make your UserForm robust in a shared environment, you need to implement a record locking system.

Step 1: Understanding Record Locking

The idea is simple:

When a user opens a record, it gets marked as “locked.”
While it’s locked, no one else can edit it.
Once the user saves or cancels, the record is unlocked.

This mimics the behavior of professional multi-user databases like Access or SQL Server, but in a lightweight Excel/VBA setup.

Step 2: Adding a Lock Column

In your Customers sheet, add a new column:

E: LockedBy

This will store the name of the user who currently has the record open. You can retrieve the user with VBA:

  Function GetUserName() As String
 
 GetUserName = Environ("Username")
 
 End Function

Step 3: Checking for Locks When Loading a Record

Before loading a record, check if another user already has it locked.

  Private Function IsLocked(rowNum As Long) As Boolean
 
 Dim ws As Worksheet
 
 Set ws = ThisWorkbook.Sheets("Customers")
 
 If ws.Cells(rowNum, 5).Value <> "" Then
 
      IsLocked = True
 
 Else
 
      IsLocked = False
 
 End If
 
 End Function

Then update LoadRecord to respect locks:

  Private Sub LoadRecord(rowNum As Long)
 
 Dim ws As Worksheet
 
 Set ws = ThisWorkbook.Sheets("Customers")
 
 If rowNum < 2 Or rowNum > ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Then Exit Sub
 
 If IsLocked(rowNum) And ws.Cells(rowNum, 5).Value <> GetUserName() Then
 
      MsgBox "This record is currently being edited by " & ws.Cells(rowNum, 5).Value, vbExclamation
 
      Exit Sub
 
 End If
 
 ' Lock the record for this user
 
 ws.Cells(rowNum, 5).Value = GetUserName()
 
 ' Load values
 
 Me.txtName.Value = ws.Cells(rowNum, 1).Value
 
 Me.txtEmail.Value = ws.Cells(rowNum, 2).Value
 
 Me.txtPhone.Value = ws.Cells(rowNum, 3).Value
 
 Me.cboType.Value = ws.Cells(rowNum, 4).Value
 
 currentRow = rowNum
 
 End Sub

Step 4: Unlocking When Done

When the user saves or cancels, clear the lock.

  Private Sub UnlockRecord(rowNum As Long)
 
 Dim ws As Worksheet
 
 Set ws = ThisWorkbook.Sheets("Customers")
 
 ws.Cells(rowNum, 5).Value = ""
 
 End Sub

Update the Save and Cancel buttons to unlock after finishing.

  Private Sub btnSave_Click()
 
 ' ... (existing save logic)
 
 Call UnlockRecord(currentRow)
 
 Me.Hide
 
 End Sub
 
 Private Sub btnCancel_Click()
 
 Call UnlockRecord(currentRow)
 
 Me.Hide
 
 End Sub

Step 5: Auto-Unlock on Form Close

To prevent permanent locks if a user just closes Excel, add:

  Private Sub UserForm_Terminate()
 
 If currentRow > 0 Then Call UnlockRecord(currentRow)
 
 End Sub

Step 6: Optional — Timestamp Locks

You can enhance the lock system by storing not just the username but also the time when the lock was set. This allows you to auto-expire locks if someone crashes out of Excel.

ws.Cells(rowNum, 5).Value = GetUserName() & "@" & Now

Then parse that string when checking locks.

Final Thoughts

By adding a record-locking mechanism, your UserForm now supports multi-user collaboration without accidental overwrites.

Users see when a record is being edited by someone else
Locks are automatically released on save, cancel, or form close
Optionally, locks can include timestamps for auto-expiry

This transforms Excel from a single-user tool into a lightweight shared app platform—perfect for small teams that need structure without deploying a full database.