How can I protect a series of say, 15 worksheets within a workbook in one go without having to individually do it by sheet. I want to protect all sheets at once and leave several input areas for other users on the front sheet open for them to sensitise. I am sure it is possible as there is an option in the list to do it, but I seem to fail. Thanks

Hi Joanna,

Thank you for your question.

There is no way of protecting multiple worksheets at once. You have to use Visual Basic code.

Before you do this, I would advise you to create a NEW BLANK WORKBOOK and make sure you have at least three worksheets and use this to test it works.

Step 1 - On the front sheet of your workbook, you need to unlock the cells you want people to enter data into. Select those cells using CTRL+click if you need to and then go to Format Cells. Select the Protection tab and take the tick out of the locked option.

Step 2 - Copy the above code from Sub ProtectAllMYSheets() to End Sub. Then go to Tools - Macro - Visual Basic Editor. In the left pane look for a bold VBA project with your filename next to it in brackets. Select it by clicking once on it. Then go to Insert - Module. Then Paste the code into that window.
----------------------------------------------------------------------
Sub ProtectAllMYSheets()
'This protects all the worksheets with the same password

Dim wMySheet As Worksheet

For Each wMySheet In Worksheets
wMySheet.Protect Password:="mysecret", UserInterfaceOnly:=True
Next wMySheet

End Sub
---------------------------------------------------------------------
Anything in green is a comment. If you want to change the password, overtype the existing one in speech marks. Then click the save icon on the toolbar and return to Excel using first button(Excel Icon) on the standard toolbar.

Then run the macro by going to Tools - Macro and select the macro and click run. This will lock all the worksheets with the same password apart from the cells on the front sheet you unlocked earlier. So all anyone can do is type into the cells you unlocked on the front sheet.

To unlock each sheet, just select that sheet and go to Tools - Protection - Unprotect sheet and put the password in.

I hope this has answered your question.

Please let me know if it works for you.

Regards

Simon