Generally, we protect the whole worksheet to Lock Cells in Excel. But we can also lock cells without protecting a sheet in Excel with VBA. Today, we will discuss 4 ideal VBA examples to lock cells without protecting a sheet. After reading the article, you will be able to lock a range of cells or a specific cell in different criteria. So, without further ado, let’s start the discussion.

Download Practice Book

Download the practice book here.

Why Do We Need to Lock Cells?

Sometimes, we need to share our excel files with other people. But there can be some important information on your excel sheet that you don’t want to get changed. For that purpose, excel users lock cells or protect the whole sheet. If we protect the whole sheet, then many options become unavailable. So, sometimes, we lock certain cells instead of protecting a whole sheet. We can lock cells without protecting a sheet in different ways. But, today, we will demonstrate examples of Excel VBA.

4 Examples of Excel VBA to Lock Cells without Protecting Sheet

To explain the examples, we will use a dataset that contains information about the Department and Date of Birth of some employees of a company. Here, we will try to lock some cells so that someone else can’t make any edits.

1. Apply VBA to Lock a Range of Cells without Protecting Sheet

In the first example, we will apply VBA to lock a range of cells without protecting a sheet in Excel. The VBA code is very easy to understand. You just need to follow the steps correctly. So, without any delay, let’s learn the steps below.

STEPS:

• In the beginning, right-click on the sheet name to open the Context Menu.
• Secondly, select View Code from there.

• It will open a Code window like the picture below.

• Now, type the code in the Code window:

Private Sub Worksheet_Change(ByVal Lock_Range As Range)
If Not Intersect(Range("C5:D9"), Lock_Range) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End If
End Sub

Here, we wanted to lock Cell C5 to D9. So, we have inserted the range inside the Intersect(Range(“C5:D9”), Lock_Range) command. For example, if you want to lock a range D1:D5, you need to write Intersect(Range(“D1:D5”), Lock_Range).

• After that, press Ctrl + S to save the code.
• Then, select Cell C5 and type Marketing in place of Sales.

• Finally, hit Enter to see no change in Cell C5. It proves Cell C5 is locked.

2. Insert VBA for Locking a Specific Cell without Protecting Sheet

In the previous example, we locked a range of cells. But you can also lock a specific cell without protecting a sheet. Here, we will use the same dataset. Let’s follow the steps below to learn more about the example.

STEPS:

• First of all, right-click on the sheet name and select View Code from the Context Menu.

• After clicking on View Code, a Code window will appear.
• Here, in the Code window, type the VBA code:

Private Sub Worksheet_Change(ByVal Specific_Cell As Range)
If Not Intersect(Range("C6"), Specific_Cell) Is Nothing Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

Here, we wanted to lock Cell C6. So, we have inserted the cell inside the Intersect(Range(“C6”), Specific_Cell) command. For example, if you want to lock a cell D1, you need to write Intersect(Range(“D1”), Specific_Cell).

• In the following step, press Ctrl + S to save the code.
• After that, try to change the value of Cell C6. We have tried to type Marketing in place of Sales.

• Next, press Enter. You will see no change in Cell C6.

• In the end, we can change the value of other cells but Cell C6 remains unchanged.

3. Lock Cells in Column or Row without Protecting Sheet with Excel VBA

We can also lock cells in a column or row without protecting the sheet with Excel VBA. Here, we will lock Cell B6 and B9 expressing them with columns and rows. In this example, you will try to select Cell B6. But the VBA code will select Cell E6 in place of B6. In this way, we can lock certain cells using columns or rows.

So, let’s pay attention to the steps below to learn more about the example.

STEPS:

• In the first place, right-click on the sheet name from the Sheet Bar and select View Code. It will open a Code window.

• Secondly, type the VBA code in the Code window:

Sub Worksheet_SelectionChange(ByVal specific_cell As Range)
If specific_cell.Column = 2 Then
If specific_cell.Row = 6 Or specific_cell.Row = 9 Then
Cells(specific_cell.Row, specific_cell.Column).Offset(0, 3).Select
End If
End If
End Sub

Here, the code states that if you select a cell from Row 6 or Row 9 from Column 2, then another cell will be selected. In this way, we can lock cells. Row 6 and Column 2 indicate Cell B6. Similarly, Row 9 and Column 2 indicate Cell B9.

• After that, press Ctrl + S to save the code.
• Now, if you select Cell B6, then Cell E6 will be selected instead of B6.

• Again, if you select Cell B9, then Cell E9 will be selected.

4. Use VBA to Lock Last Edited Cell without Protecting Sheet

In the last example, we will show to lock the last edited cell without protecting the sheet. It is quite similar to the previous examples. You need to enter the cell manually in the code. Automatic locking is possible with protection. Again, we will use the same dataset here. Let’s observe the steps below to learn more.

STEPS:

• Firstly, right-click on the sheet name and select View Code from the Context Menu. It will open the Code window.

• After that, type the code in the Code window:

Private Sub Worksheet_SelectionChange(ByVal LastEdit As Range)
If LastEdit.Column = 4 Then
If LastEdit.Row = 9 Then
Beep
Cells(LastEdit.Row, LastEdit.Column).Offset(0, 1).Select
End If
End If
End Sub

Here, the last edited cell is D9. The row index of Cell D9 is 9 and the column index is 4. If you select any other cell, then you need to change these values in the code.

• Now, press Ctrl + S to save the code.
• Finally, if you select Cell D9, then, Cell E9 will be selected. Thus, Cell D9 will be locked.

Things to Remember

There are certain things you need to remember when you are trying to lock cells without protecting the sheet.

• The above examples are quite similar and have small differences.
• Be careful when you are using row and column indexes to indicate a cell.
• Protect the sheet manually if necessary.

Conclusion

In this article, we have demonstrated 4 ideal examples of Excel VBA to Lock Cells without Protecting Sheet. Here, we have discussed 4 ideal examples to explain the process. I hope these examples will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

The post Excel VBA to Lock Cells without Protecting Sheet (4 Ideal Examples) appeared first on ExcelDemy.