Protecting (and unprotecting) sheets
It is often a good idea to protect sheets in your workbook. This stops users (including yourself) from making unintended changes by typing over vital formulae. You can even protect sheets that contain input cells; if you unlock the input cells, users are prevented from making any changes elsewhere, while still being able to enter values into the input cells.
Sheets can be protected either with or without passwords. A sheet that has been protected without a password can be unprotected by anybody, simply by using the Tools > Protection > Unprotect Sheet... command (note this command appears on the menu only if the sheet is in fact protected). One that has been protected with a password is unprotected using the same command, but the password must be entered at the prompt.
Even protecting sheets without a password can provide a safety margin. It means that the user has to think before making a possibly undesirable change. Protecting sheets with a password provides a further safety margin, but against this must be set the possibility of forgetting the password.
However, there are several problems with protecting sheets.
Each sheet has to be protected individually, using the Tools > Protection > Protect Sheet... command. Selecting several sheets at once does not work. There is no way to specify a set of sheets that should generally be protected.
In order to unprotect sheets the Tools > Protection > Unprotect Sheet... command must be performed separately for each sheet. Selecting several sheets at once does not work.
Both these problems can be overcome by using the XLSior > Operate on sheets > Protect Sheets... command.
Protecting sheets
The XLSior > Operate on sheets > Protect Sheets... command displays a screen that lets you choose which sheets should be protected and which should be unprotected. You can specify separate passwords to use for protecting and unprotecting. When you click on the OK button the sheets that you have selected will be treated accordingly.
The screen contains three lists. The left hand list is for the sheets that you want to be unprotected and the right hand list is for the sheets that you want to be protected. The middle list is for sheets that you don't care about; sheets on this list will be left as they are. Note that the screen does not reflect the current state of the sheets in any way. It is intended to let you choose what will happen to the sheets, regardless of whether they are currently protected or not.
You choose which sheets should be protected or unprotected by moving them into the relevant list using the buttons between the lists. You can select more than one sheet at a time in either list by using the control or shift key while clicking in the usual way.
The sheets you choose to protect or unprotect will be remembered the next time you run this command. This allows you to have a set of sheets that are generally protected, and another that are generally unprotected; all you have to do is to bring up the dialog and click the OK button without making any changes.
If any sheets that you wish to be unprotected are protected using a password, and you did not specify the correct password when choosing sheets, a prompt will appear. The prompt itself does not say for which sheet the password is required (this is a limitation of Excel) but there will also be another window visible that does give the name.
Sheets that are used by XLSior will not appear in any of the lists, as they should not be protected or unprotected manually. The sheets that will not be listed are:
- The listing sheets: X~Sheets, X~Names, X~Comments and X~Links
- The internal sheet: X~Internal
- Import sheets: those whose name starts with X~Imp
- The test results sheet: X~TestResults
- Test sheets: those whose name starts with X~Test
All of these sheets, except the test sheets, are protected by XLSior and should not be unprotected by the user. The test sheets must be unprotected in order for the tests to run. If any are found to be protected when running the tests, XLSior will attempt to unprotect them, asking you for a password if necessary.
You can also protect all sheets, other than those used by XLSior, by clicking on the Protect All button. This will protect all sheets, regardless of which list they are on. Similarly you can unprotect all sheets (other than sheets used by XLSior) by clicking on the the Unprotect All button. If you use either of these buttons no changes you may have made to the lists of sheets will be remembered.
You cannot use the XLSior > Operate on sheets > Protect Sheets... command on a shared workbook.