Locking a cell does nothing until the sheet is protected, and a sheet password stops accidents, not people. This tutorial protects the quotation from the previous tutorial: which cells stay open, which formulas to hide, why the data sheets stay unprotected, and a check that catches the overwrite that gets past the lock.

What each protection stops

Excel has four layers that people mix up. Only the last one keeps a file closed.

Layer Where It stops It does not stop
Protect Sheet Review, Protect Sheet Typing over locked cells Anyone who unprotects it
Hidden formulas Format Cells, Protection Seeing the formula in the formula bar Seeing the value
Protect Workbook Review, Protect Workbook Renaming, moving, hiding or deleting sheets Editing cells
Encrypt with Password File, Info, Protect Workbook Opening the file Anything, once it is open

Lock the quotation in two minutes

Every cell is Locked by default, and the setting has no effect until the sheet is protected; the dialog says so itself. So the work is the reverse of what it sounds like: you unlock the cells people type in.

Format Cells, Protection tab, with the Locked and Hidden boxes and a note on protecting the sheet / Formato de celdas, pestaña Protección, con Bloqueada, Oculta y un aviso sobre proteger la hoja

  1. Select the input cells with CTRL + click: B8, B11, B13, B14 and H10. Press CTRL + 1, open the Protection tab and clear Locked.
  2. Select the pricing formulas, such as the totals in H9 and H11:H13, press CTRL + 1 and tick Hidden.
  3. Go to Home, Format, Protect Sheet. Leave the password blank while you test. Home, Format menu with Protect Sheet and Lock Cell under Protection / Menú Inicio, Formato con Proteger hoja y Bloquear celda en Protección

Protect Sheet dialog with the password box, Select locked cells and Select unlocked cells checked / Cuadro Proteger hoja con la contraseña y Seleccionar celdas bloqueadas y desbloqueadas marcadas

The two Select options stay ticked so people can click any cell. Tick the other permissions only for what people really do: Use AutoFilter if they filter a list, for example, and leave the rest cleared. Now type in B14 and the quantities change. Try a formula cell and Excel refuses:

Excel message saying the cell or chart is on a protected sheet and must be unprotected to change it / Mensaje de Excel que dice que la celda o el gráfico está en una hoja protegida y hay que desprotegerla para cambiarlo

A Hidden cell still shows its value but the formula bar stays empty:

Earlier version of the quotation sheet with a hidden cell selected and the formula bar empty / Versión anterior de la hoja de cotización con una celda oculta seleccionada y la barra de fórmulas vacía

Leave the data sheets unprotected

In the quotation, only the Quotation sheet is protected. Clients, Materials, Products and Issuers are Tables, and a Table does not grow on a protected sheet: protect Clients, try to add a client below the last row, and Excel will not extend it. Protect the sheet where people type and leave the sheets that people extend; the workbook has a Map sheet that lists what is protected on each sheet and why.

A check for what the lock misses

A blank password is one click to remove. If someone unprotects the sheet and types a number over a total, nothing in Excel shows it. The formula check in H14 does:

=IF(AND(ISFORMULA(H9),ISFORMULA(H11),ISFORMULA(H12),ISFORMULA(H13),ISFORMULA(A17),ISFORMULA(D17),ISFORMULA(E17)),"OK","Overwritten")

It tests the formula cells and the anchor cell of each spilled list. Type 0 over H12 and it says Overwritten; undo and it returns to OK.

If a macro edits the protected sheet

A macro that changes a protected sheet has to unprotect it first. If the code fails halfway, the sheet is left unprotected. Put the protection back in a cleanup label:

Sub UpdateQuotation()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Quotation")
    ws.Unprotect "yourpassword"
    On Error GoTo Cleanup
    ' code that changes the sheet
Cleanup:
    ws.Protect "yourpassword", DrawingObjects:=False, Scenarios:=False
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

DrawingObjects:=False and Scenarios:=False leave form controls usable. A password typed in the macro is as visible as the macro.

Quotation sheet with the unlocked input cells B8, B11, B13, B14 and H10 shown in blue on a pale yellow fill / Hoja Quotation con las celdas de captura B8, B11, B13, B14 y H10 desbloqueadas, en azul sobre fondo amarillo claro

Quotation sheet with the totals cell H9 selected, an empty formula bar and the formula check reading OK / Hoja Quotation con la celda de totales H9 seleccionada, la barra de fórmulas vacía y el control de fórmulas en OK

Limits

  • A sheet password is not security. It prevents accidents; someone with the file and some effort can remove it. Do not rely on it for confidential data.
  • Only Encrypt with Password keeps the file closed. If you forget that password, the file is lost.
  • Hidden formulas still show their values. Do not hide a formula that a reviewer or an auditor needs to follow.
  • Protect Workbook locks the structure, not the cells. It stops sheet changes; it does not stop edits.
  • The file has no password on purpose, so you can study it. Add one before you share it with a team.

Workbook

Download the protected quotation workbook, with fictitious data, no sign-up and no email. It has a Map of what is protected on each sheet and nine guided tests. The quotation itself is built in Beyond Excel Formulas: Build an Effortless Quotation System.

If you want a workbook protected for your team, let's talk.