How do I protect all worksheet in an Excel workbook with a single click?

Question

I have around 25 worksheets in my workbook (Excel spreadsheet). Is there a way I can protect all the 25 worksheets in single click ? or this feature is not available and I will have to write a VBA code to accomplish this. I need very often to protect all sheets and unprotect all sheets and doing individually is time consuming

Answer 1

I don't believe there's a way to do it without using VBA. If you are interested in a VBA solution, here is the code:

Dim ws as Worksheet
Dim pwd as String

pwd = "" ' Put your password here
For Each ws In Worksheets
    ws.Protect Password:=pwd
Next ws

Unprotecting is virtually the same:

Dim ws as Worksheet
Dim pwd as String

pwd = "" ' Put your password here
For Each ws In Worksheets
    ws.Unprotect Password:=pwd
Next ws

Answer 2

Don't think there's a button to do it, but it's simple enough code:

For Each protSheet In Worksheets protSheet.Protect Password := "boo" Next protSheet

Answer 3

You can protect the workbook rather than each sheet and this will stop changes being made across the entire workbook