How to protect an entire worksheet in an Excel workbook with one click?

Question

I have about 25 sheets in my workbook (Excel spreadsheet). Is there a way to protect all 25 worksheets with one click? or this function is not available, and I will need to write VBA code for this. I very often need to protect all sheets and remove protection from all sheets, and individual execution takes a lot of time.

Answer 1

I do not believe that there is a way to do this 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 

Removing protection is almost 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

Do not think that there is a button for this, but rather simple code:

For each profile in protSheet.Protect Password: = "boo" Next protSheet

Answer 3

You can protect the book, not every sheet, and this will stop making changes to the entire book

Answer 4

You can protect all sheets from user changes, but still allow VBA scripts to make changes using the "UserInterfaceOnly" option. This workaround allows you to run any VBA script on sheets without having to protect and unprotect each time:

 Dim ws as Worksheet Dim pwd as String pwd = "" ' Put your password here or Each ws In Worksheets ws.Protect Password:=pwd, UserInterfaceOnly:=True Next ws 

Deprotection is the same as the solution proposed by Ben Hoffstein:

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

You can access this macro using the / shortcut button. In Excel 2010, you right-click on the shortcut bar and select "Customize Shortcut Bar". In the drop-down menu, select the command, select "Macros". Then click on the VBA script that you created to protect (or unprotect). Finally, click "Add →" and then "OK" to save it.