Microsoft

Keep users from selecting locked cells in Excel

Disabling the selection for a protected sheet is a great way to guide users through an input range. If users disable the input range, use this macro to automatically reset it.

Most of us create custom workbooks that others update. You probably protect the sheets and unlock only the input cells. That way, users can't accidentally delete or change formulas and other flag values. The worst they can do is enter invalid values.

Unlocking input cells and protecting sheets is a simple enough process, but a truly knowledgeable user can get around it. For those users, there's a simple macro for resetting things. First, let's unlock input cells in the simple sheet shown below.

In this particular sheet, users only need to update two cells: B1 and B2. You'll want to unlock your input cells, as follows, before you protect the sheet:

  1. Select the input cells. In this case, that's B1:B2.
  2. Right-click the selection and choose Format Cells from the resulting context menu.
  3. Click the Protection tab.
  4. Uncheck the Locked option.
  5. Click OK.

The next step is to protect the sheet as follows:

  1. From the Tools menu, choose Protection, and then select Protect Sheet. In Excel 2007 and 2010, click the Review tab | Protect Sheet (in the Changes group).
  2. Enter a password.
  3. Uncheck the Select Unlocked Cells option.
  4. Click OK.
  5. Enter the password a second time to confirm it.
  6. Click OK.

At this point, you can select and change the contents of cells B1 and B2. You can't select any other cells but B1 and B2.

As I mentioned, it won't always matter if a user can select locked cells. On the other hand, the setup I'm suggesting creates an easy-to-follow data entry map. There's no confusion for the user—the only updateable cells are those the user can select.

This much you might already know. What's a bit scary is that a user can quickly undo the selection property as follows:

  1. From the View menu, choose Toolbars.
  2. Select Control Toolbox.
  3. Click the Properties tool.
  4. In the properties window, change the EnableSelection property to 0-xlNoRestriction.
  5. Click OK.

Users can also access this property via the VBE. In Excel 2007 and 2010, the user can display the Developer tab (via the File | Customize Ribbon route) and click Properties in the Controls group.

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!
After resetting the EnableSelection property to 0, users can select any cell in the sheet, but they still can't alter cell contents, except for the cells you unlocked before protecting the sheet. This doesn't seem all that important, unless your users don't know what they're supposed to do. In this simple sheet, 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 resets the property when the workbook is opened and a second 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 = xlUnlockedCells
End Sub

That macro will reset the property when the workbook is opened. That way, users always start with the right 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("IndirectEx").EnableSelection = xlUnlockedCells
End Sub

The only difference is the event that executes each macro. The SelectionChange event fires when a user changes the cell selection (only in the specified sheet, not throughout the entire workbook). Users won't notice it at all unless they manage to disable the EnableSelection property (as described earlier). Then, the user will be able to select a locked cell. Doing so will execute the macro, which will reset the property.  The user will be able to select only one locked cell before the macro resets the property.

The truth is the user that's smart enough to get around your locked cells might know how to circumvent your macros— but they're worth a try.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks