98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Protection of workborks
Protection of workborks
Resolved · Low Priority · Version 2003
Joanna has attended:
Excel Intermediate course
Excel Advanced course
Protection of workborks
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
RE: protection of workborks
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
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Date and timeCTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON |