Pages

Lock Only Specific Cells In An Excel Worksheet

In the previous lesson we learnt how to protect a worksheet. By default all the cells of worksheets are locked. So when we protect the sheet, we can not change the values of any cells. But sometimes we need to lock only specific cells of a worksheets. Then we will be able to change values in cells we need. So In this lesson you can learn how to lock only specific cells in an Excel worksheet. Let’s consider this sample Excel sheet.


Sample data entry application interface

This is a data entry interface of a simple VBA project. Here the data entry interface is designed on a worksheet instead of a VBA form. Column B of this sheet has field names. And grey color cells of column C are used to get the user input. Suppose that we want to lock only field names of the worksheet. Then users can enter the data to relevant cells of column C. But they will not be able to change the field names in column B. Now let’s look at how to do this.

First select the cells which you don’t want to lock. In this example we need to select the grey color cells of the column C.

Select cells you don’t want to lock

Then right click on one of selected cells and select “Format Cells…” from the shortcut menu.

Select Format cells from the shortcut menu

Then go to the “Protection” tab of the “Format Cells” window. Uncheck the “Locked” option.

Uncheck locked option

Now we need to protect the sheet. You can follow the steps explained in our previous post to protect the sheet.

How To Protect A Worksheet In Excel

Then even after protecting the sheet, we can enter values to the grey color cells we unlocked. But Excel will display this message if we try to change any other cells.

Warning message