Microsoft Office

Protect Existing Data in Excel While Still Allowing New Entries

Unlock the entry range, protect the sheet with a password, and add a Worksheet_Change macro that locks each cell as soon as data is entered.

Inder Kochar系统与网络工程师更新于 October 5, 2026 2 分钟阅读 Intermediate

测试环境 Excel 2019, Excel for Microsoft 365

For logs, registers and shared trackers you often want an “append-only” sheet: anyone can add a new entry, but nobody can quietly change or delete earlier ones. Excel can do this with sheet protection plus a short macro that locks each cell once it has been filled in.

Step 1 – Unlock the entry area

Select the range people will type into (for example A2:M2000).
Press Ctrl + 1 to open Format Cells.
On the Protection tab, clear Locked and click OK.

Step 2 – Protect the sheet

Go to Review → Protect Sheet.
Enter a password, keep “Select unlocked cells” ticked, and confirm the password.

Step 3 – Add the macro

Right-click the sheet tab and choose View Code. Paste the code below into the sheet’s code window, changing the range and the password to match yours:

Sheet code (VBA)

Private Sub Worksheet_Change(ByVal Target As Range)n    Dim entered As Rangen    On Error GoTo Donen    Set entered = Intersect(Me.Range(u0022A2:M2000u0022), Target)n    If entered Is Nothing Then Exit Subn    Me.Unprotect Password:=u0022YourPasswordu0022n    entered.Locked = Truen    Me.Protect Password:=u0022YourPasswordu0022nDone:nEnd Sub

Step 4 – Save and test

Save the workbook as Excel Macro-Enabled Workbook (.xlsm) – a normal .xlsx drops the macro.
Type into an empty cell in the range: it accepts the value and then locks.
Try to edit or clear that cell – Excel refuses because the sheet is protected.

Limits of this approach

Sheet passwords stop accidental changes, not a determined user – the password is visible in the VBA code unless you also lock the VBA project (Tools → VBAProject Properties → Protection). Macros must be enabled. For audit-grade records, use a database or SharePoint list with version history.

To correct a mistake later, unprotect the sheet with your password (Review → Unprotect Sheet), fix the cell and protect the sheet again.