Categories
Excel

Lock and Protect Cells Containing Formulas

You may want to let users change cells containing data without providing them access to change formulas. You can keep cells containing formulas under lock and key without having to protect your entire sheet or workbook.

By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.

  1. Unlock all cells.
    Select Cells Containing Formulas with Go To Special
  2. Lock cells containing formulas.
  3. Protect the sheet and content of locked cells.
    Allow formatting on unlocked cells on a protected sheet
  4. Using VBA to Protect Sheet If Locked Cells Found

1. Unlock Cells

  1. Select all cells on your worksheet by pressing Ctrl+A or ⌘+A.
  2. Right-click on any cell and choose Format Cells from the context menu.
  3. Go to the Protection tab and uncheck the Locked option.
  4. Click OK.

After removing the lock for all cells, your next step is to select only the cells that contain formulas that you want to lock and protect.

Select Only Cells That Contain Formulas

The Go To Special can select all of the formula cells in your worksheet with few clicks:

  1. Select any single cell on the sheet.
  2. Press Ctrl+G or F5 to open Go To dialog box.
  3. Click Special to open the Go To Special dialog box.
  4. Select Formulas
    If needed, limit the formulas to the subtypes underneath.
  5. Click OK.

After selecting formula cells, your next step is to lock and protect these cells.

2. Lock Cells Containing Formula

Now we repeat the steps we did to unlock all cells, but this time we lock cells containing formulas. With only the formula cells selected:

  1. Right-click on a selected formula cell and choose Format Cells from the context menu.
  2. The Format Cells dialog box will be displayed.
  3. Go to the Protection tab and CHECK the Locked option.
  4. Click OK.

After locking formula cells, your next step is to protect the worksheet to enabled protection for LOCKED cells.

3. Protect Worksheet

Now go to the Review tab and click on Protect Sheet. You’ll see a dialog box such as that in the figure:

Apply a password if required and click OK.

The preceding method certainly saves a lot of time and eliminates possible errors in locating formulas so that you can protect them.

Allow formatting on unlocked cells on a protected sheet

Unfortunately, Protect Sheet command can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell.

Formatting changes, aligning text, and many other features are not available

To overcome this problem, check all or relative options under the Allow all users of this worksheet to: in the Protect Sheet dialog box:

The Protect Sheet dialog box has several options, which determine what the user can do when the worksheet is protected:

  1. Select Locked Cells
    Allow users to select locked cells.
  2. Select Unlocked Cells
    Allow users to select unlocked cells.
  3. Format Cells
    Allow users to apply formatting to cells including locked cells. If you want to prevent applying formatting on locked cells uncheck the Select Locked Cells option.
  4. Format Columns and Format Rows
    Allow users to hide or change the width/height of columns/rows. You can not format rows/columns if they contain locked cells and Select Locked Cells option is unchecked.
  5. And many other self-explanatory options.

Using VBA to Protect Sheet If Locked Cells Found

The last method also will enable you to use all of Excel’s features, but only when you are in a cell that is not locked. To start, ensure that only the cells you want protected are locked and that all other cells are unlocked.

Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Locked = True Then
        Me.Protect
    Else
        Me.Unprotect
    End If
End Sub

If a password is used, use Password:="YourPassword“. Change the word YourPassword to your password. Press Alt+Q or ⌘+Q or click the X in the top right-hand corner to get back to Excel and save your workbook. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Locked = True Then
        Me.Protect Password:="YourPassword"
    Else
        Me.Unprotect Password:="YourPassword"
    End If
End Sub

Now, each time you select a cell that is locked, your worksheet will automatically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself.

This code doesn’t work perfectly, though it usually works well enough. The keyword used in the code, Target, will refer only to the cell that is active at the time of selection. For this reason, it is important to note that if a user selects a range of cells (with the active cell being an unlocked cell), it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet automatically will unprotect itself.

Understanding Workbooks and Worksheets: