Protect All Sheets With Password

What it does

Excel's sheet protection is per-tab. To protect a 30-tab workbook with the same password, you click each tab, Review > Protect Sheet, type the password, confirm, repeat 30 times. The macro skips the loop. Type the password once, confirm it once, the macro applies the same protection to every sheet in the active workbook.

Important warning: if you forget the password, the protection is hard to remove. There are external tools that can crack short passwords, but it's a hassle. Pick a password you can remember (or store in your password manager) before running.

Real-world example

Picture a senior auditor at a Big-4 firm preparing a client's audited financial statements workbook for delivery. The workbook has 22 tabs: trial balance, leadsheets, footnote support, financial statements, opinion letter. Firm policy: every tab gets sheet-level protection with the firm's audit-period password before delivery, so the client can read but can't modify cells.

His current method: walk each of the 22 tabs, Review > Protect Sheet, type password, type confirm, OK. About 6 minutes of repetitive clicking. Most weeks he forgets the password somewhere around tab 15 and has to start over.

With this macro he runs the tool, types the firm's audit password (from the password manager), confirms, and 22 sheets get protected in 1 second. The popup confirms count.

On a 22-tab audit binder, expect about 5 minutes saved per delivery. The bigger win is consistent protection: every tab has the same password, no missed tab where someone could edit data.

How to use

First time? See How to run our tools for the 30-second setup.

1. Press Alt + F8 (Windows) or Option + F8 (Mac), pick ProtectAllSheets, click Run.
2. Type the password to apply to every sheet. (Leave blank for protection without a password, which only blocks accidental edits and not deliberate ones.)
3. Re-type the password to confirm.
4. The macro applies sheet protection to every sheet. The popup reports the count.

FAQ

What if I forget the password?

Excel sheet protection is not strong cryptography; it can be cracked with external tools, but it's a hassle and not always quick. The safe play: store the password in your password manager BEFORE running this macro. If you do forget, search for "Excel sheet protection remover" — there are free utilities.

Does it protect very-hidden sheets too?

Yes. The macro processes every sheet in ActiveWorkbook.Worksheets, regardless of visibility. Hidden and very-hidden sheets get the same protection as visible ones.

What level of protection does it apply?

The default Excel protection: users can't modify cells, change formatting, insert/delete rows or columns, or edit objects. They CAN select cells (for copy purposes). For more restrictive protection (no select, no copy), edit the .bas source: change ws.Protect Password:=password to ws.Protect Password:=password, AllowSelection:=False etc.

Can I have different passwords for different sheets?

No, this macro applies one password to all sheets. For per-sheet passwords, you'd write a different macro that prompts per tab. We may add a "tab-by-tab" mode in a future version if folks ask.

Will it protect sheets that are already protected?

It tries to. If a sheet is already protected with a different password, the macro errors on that sheet. Run Unprotect All Sheets first if you need to reset.

What to do next

For the reverse operation (taking protection off), use Unprotect All Sheets. Both macros are paired, so you can lock down a workbook for delivery and unlock it for editing without per-sheet clicking.