There are many of us who work in an office environment creating custom workbooks in MS Excel that our colleagues update.We protect our worksheets and only unlock the necessary input cells so that our colleagues cannot accidentally delete or change the formulas and other values.
Unlocking the input cells and protecting your worksheets is an easy enough process but a truly savvy user can get around that and can still accidentally wreak havoc with your custom workbook. For those savvy users there is a simple macro for resetting things.
First, let us unlock the input cells in a worksheet. Look at the example below.
In this sheet, your colleagues need only update two cells: B1 and B2.
- Right-click the selection and select Format Cells from the resulting context menu.
- Click the Protection tab.
- Uncheck the Locked option.
- Click OK.
Now we will protect the sheet. Follow the steps below to learn how.
- Click the Review tab | Protect Sheet in the Changes group.
- Enter a password.
- Uncheck Select Unlocked Cells.
- Click OK.
- Enter the password a second time to confirm it.
- Click OK.
You can now select and change the contents of cells B1 and B2. You cannot select any other cells but B1 and B2.
There is now no confusion for your colleague – the only cells that can be updated are the ones that one can select.
What causes concern though, is that a user can very quickly undo the selection property.
Users can also access this property via the VBE. In versions 2007 and 2010, the user can display the Developer tab using the File | Customize Ribbon method and click Properties in the Controls group.
After resetting the EnableSelection property to o, users can select any cell in the worksheet although they cannot alter cell content, excepting the cells you unlocked prior to protecting your sheet. In the example worksheet, the input cells are clear but a complex sheet with non-contiguous input ranges will certainly be more confusing.
To reclaim the original settings, include two macros: One that will reset the property when your workbook is opened and a second one that resets the property when the selection in the sheet changes.
Open the Visual Basic Editor and double-click ThisWorkbook in the Project Window. Then, enter the following macro:
Private Sub Workbook_Open ()
‘Disable locked cells in IndirectEx sheet.
Worksheets (“IndirectEx”) .EnableSelection = x1UnlockedCells
That macro will reset the property when your workbook is opened. In that way, users always start with the correct setting. To add the macro that acts on a selection change in the actual sheet, double-click the sheet by name in the VBE Project window and enter this macro:
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
‘Reset if user manages to disable enable selection property.
Worksheets (“Indirect Ex”) .EnableSelection = x1UnlockedCells
The only difference is the event that executes each macro. The SelectionChange event fires when a user changes the cell selection (only in a specified sheet, not throughout the workbook.
Users will not notice it at all unless they manage to disable the EnableSelection property described above. In that event, the user will be able to select a locked cell. Doing so will execute the macro, which will reset the property. The user will only be able to select one locked cell before the macro will reset the property.
Honestly, if the user is savvy enough to get around your locked cells, he/she may know how to circumvent your macros as well but at least you have made it a lot more difficult!