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:
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 SubStep 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.
Escrito por
Inder Kochar
Ingeniero de sistemas y redes – Documento las soluciones que aplico con mis clientes, probadas en sistemas reales. ¿Atascado con algo parecido?
