While Data Validation controls what can be entered in cells, Protect Sheet controls what cells can be entered, and other sheet-level actions. It has fifteen options of what can be allowed once protection is turned on, with an optional password. This gives it quite a range of control, from mild to strict.
As with Data Validation dropdowns, Protect Sheet offers more than its name implies. It is a navigational aid that can make a sheet behave like a data form. When only input cells are unlocked and protection is turned on, the user can hit Tab or Enter to navigate from one input field to the next.
The first step is to set cell protection. The default is protected, and any cell that the user inputs, or that any control links to, must be unlocked. Use fill color so that only the unlocked interactive cells are white. Store control-linked cells on another sheet so that the user can tab through input fields without landing on a control’s cell.
To make the sheet act like a form, use Protect Sheet, Alt-H,O,P or Alt-R,P,S, without a password. Omitting a password makes it possible for anyone to unprotect the sheet, but it avoids problems and is really meant to protect the user from inadvertent errors, as well as to aid navigation. Most of the permissions can be left off. Always allow “Select unlocked cells”, and decide whether to allow “Select locked cells” or not for the desired behavior, as described next.
With locked cells allowed, Tab cycles through all unlocked cells, across then down, and all other navigation keys behave normally. With locked cells disallowed, Tab and → cycle through unlocked cells, across then down, while Enter and ↓ cycle through unlocked cells, down then across. This makes the sheet nearly foolproof with little or no instruction needed.
If the sheet has Slicers, allow Edit objects. If they are Table Slicers, allow Use AutoFilter, and if they are Pivot Slicers, allow Use PivotTable & PivotChart. Always check every intended function and control on the sheet to be sure the user will not encounter frustrating obstacles.
If there is a data entry table that requires inserting and deleting rows, Protect Sheet is not a good solution. Even with all actions allowed, insert and delete of table rows does not work. The entire sheet row can be inserted, but cannot be deleted unless the entire row is unlocked. Since Tab navigation is already active in tables, the Protect Sheet navigational advantage really applies to non-table ranges, or to tables with a static structure.
There may be cases that you want to password-protect, for example, if you store sensitive data or quiz answers in a sheet. Protect Sheet cannot disallow hide/unhide of the sheet, but you can either hide the columns or rows on the sheet before protecting it, or you can Protect Workbook, Alt-R,P,W.
Comments