Tips

Tips – Locking only cells in Excel

I looked high and low for a way to lock isolated cells in Excel without a need to unprotect the whole worksheet whenever any cell was being edited, and I finally came across two options; the first was my preference:

Option 1:

To ONLY protect or lock individual cells in Excel without locking the whole worksheet

  1. Select the whole worksheet using either ‘Ctrl + A’ or click in the top left corner of the sheet (little pale arrow).
  2. Right click any cell and select “Format Cells …”. In the Protection tab, remove the checkmark next to “Locked”. Click OK.
  3. Now select the cells or the range that you want to protect. Right-click on it and select “Format cells …”.
  4. In the Protection tab, check the box next to ‘Locked’.
  5. Once the cells you want to lock are ticked, go to the tab and select ‘Review’, then ‘Protect Sheet’.
  6. Ensure you tick ‘Select locked cells’ and ‘Select unlocked cells’ in the ‘All users of this worksheet…’ submenu.
  7. Assign a password and confirm twice.

Note that the password will only be needed if unlocking the worksheet to edit the locked cells. If other unlocked cells are being manipulated, there will be no need to unlock/unprotect the worksheet.

To summarise, unlock the whole sheet first, then lock the cells you want protected, then protect the sheet.

Option 2:

You can use VBA code to prevent some cells from being edited, but that only helps with accidental edits. Someone who knows a bit about Excel can easily circumvent it by either opening the workbook without allowing macros, or by temporarily disabling events.

  1. Right-click the sheet tab.
  2. Select ‘View Code’ from the context menu.
  3. Copy the code listed below atm no 8 into the worksheet module.
  4. Change A2,C2,A4,C4 to the list of cells that you want to protect. You can use ranges too: A2:B10,D2:H10
  5. Switch back to Excel.
  6. Save the workbook as a macro-enabled workbook (*.xlsm).
  7. Instruct users to allow macros when they open the workbook.
  8. Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range(“A2,C2,A4,C4”), Target) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    End If
    End Sub

About Phoenix

Accountant | Tax Specialist | Dreamy Entrepreneur | Blogger

Discussion

No comments yet.

Get in touch

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Enter your email address to receive notifications of new posts by email.

Join 851 other subscribers
Follow Phoenix Debola on WordPress.com
%d bloggers like this: