Excel VBA to Lock Cells without Protecting Sheet (4 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

Generally, we protect the whole worksheet by locking 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.


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.


Excel VBA to Lock Cells without Protecting Sheet: 4 Examples

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.

Apply VBA to Lock a Range of Cells without Protecting Sheet

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

Apply VBA to Lock a Range of Cells without Protecting Sheet

  • 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

Apply VBA to Lock a Range of Cells without Protecting Sheet

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.

Apply VBA to Lock a Range of Cells without Protecting Sheet

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

Apply VBA to Lock a Range of Cells without Protecting Sheet

Read More: Excel VBA to Protect Range of Cells


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.

Insert VBA for Locking a Specific Cell without Protecting Sheet

  • 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

Insert VBA for Locking a Specific Cell without Protecting Sheet

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.

Insert VBA for Locking a Specific Cell without Protecting Sheet

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

Insert VBA for Locking a Specific Cell without Protecting Sheet

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

Insert VBA for Locking a Specific Cell without Protecting Sheet


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.

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

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.

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

  • 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

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

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.

Read More: Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking


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.

Download Practice Book

Download the practice book here.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo