Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Lock and Unlock Cells in Excel Using VBA

Locking cells, columns, and rows in excel give you the freedom to protect your data. After protecting a worksheet, by default, every cell is locked. It means that they can not be edited. But protecting different cells gives you the freedom to work on unprotected cells. Here, you’ll see different excel techniques for locking and unlocking cells. There are many default Excel Functions that we can use to create formulas. Again, locking cells will have no impact if you don’t protect the worksheet. This article will show you 6 ideal examples to Lock and Unlock Cells in Excel Using VBA.


Download Practice Workbook

Download the following workbook to practice by yourself.


6 Ideal Examples to Lock and Unlock Cells in Excel Using VBA

Excel VBA can perform many complicated tasks very easily. You’ll just need to input a code or write one. In this article, we’ll apply VBA to Lock and Unlock Cells in Excel. To illustrate, we’ll use a sample dataset. For instance, the following dataset contains Salesman, Product, and Net Sales in the cells range B4:D10. Here, we’ll lock different cell ranges.

how to lock and unlock cells in excel using vba


1. Lock All Cells in Excel Worksheet with VBA

In our first example, you’ll see how to lock all cells in an excel worksheet. Therefore, follow the steps below to perform the task.

STEPS:

  • First of all, go to Developer ➤ Visual Basic.

Lock All Cells in Excel Worksheet with VBA

  • As a result, the VBA window will pop out.
  • Now, select Insert ➤ Module.

  • Consequently, the Module dialog box will appear.
  • Next, copy the below code and paste it into the Module box.
Sub AllCells()
Sheets("All Cells").Cells.Locked = True
Sheets("All Cells").Protect
End Sub

  • Save the file and press the F5 key to run the code.
  • Hence, when you try to edit or modify any cell, you’ll get a warning dialog box as shown below.


2. Apply Excel VBA for Locking Specific Cells

Now, we’ll show the code to lock specific cells. We’ll also insert a check box that will control the locking and unlocking. So, learn the following steps to carry out the operation.

STEPS:

  • Firstly, name the cell range B4:D10 as Table1.

Apply Excel VBA for Locking Specific Cells

  • Next, click Developer ➤ Insert ➤ Check Box.

  • Afterward, place the check box where you want to.
  • Then, right-click on the sheet name.
  • Select View Code from the context menu.

  • Input the below code in the pop-out dialog box.
Private Sub CheckBox1_Click()
Range("Table1").Select
On Error Resume Next
If CheckBox1.Value = True Then
    Selection.Locked = False
    MsgBox Selection.Address & " Cell Range is Unlocked", vbInformation, "ExcelDemy"
Else
    Selection.Locked = True
    MsgBox Selection.Address & " Cell Range is Locked", vbInformation, "ExcelDemy"
End If
End Sub

  • Save the file and run the code by pressing the F5 key.
  • Thus, checking the box will unlock the cells and return a dialog box as demonstrated below.

  • Similarly, unchecking the box will lock the cells.


3. Lock Only Cells with Formulas Using VBA

However, we can lock only cells where there is a formula. Hence, follow the process to lock cells with a formula.

STEPS:

  • First, get the Module box by following the steps in example 1.
  • Type the below code in the box.
Sub LockCellsWithFormulas()
For Each Rng In ActiveSheet.Range("B5:D10")
If Rng.HasFormula Then
Rng.Locked = True
Else
Rng.Locked = False
End If
Next Rng
ActiveSheet.Protect "111"
End Sub

Lock Only Cells with Formulas Using VBA

  • After that, click on the RunSub button to run the code.
  • Here we can see that this code will lock cells where we have input a formula.
  • For example, D7 has a formula. So we can’t edit this cell.


4. Lock Entire Worksheet Except for Few Cells

Moreover, we can lock the entire worksheet except for a few cells which we’ll specify. Look at the below code where B5:D10 is the range we want to keep unlocked. Other than this range, the whole worksheet will get locked after running the code.

Sub WorksheetExceptFewCells()
Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Sheets("Entire Worksheet Except Few").Range("B5:D10").Locked = False
wb.Sheets("Entire Worksheet Except Few").Protect passowrd = "111"
End Sub

Employ VBA to Lock Entire Worksheet Except for Few Cells


5. Lock Cells after Data Entry in Excel

Again, it’s necessary to lock cells after entering the data in the desired range. In this example, B4:D10 is the range where we’ll input the data. Therefore, follow the steps below to lock cells after data entry in excel.

STEPS:

  • Firstly, right-click on the sheet name and select View Code.
  • Input the following code in the dialog box.
Dim rg As Range
Dim str As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("B4:D10"), Target) Is Nothing Then
    Set rg = Target.Item(1)
    str = rg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xg As Range
    On Error Resume Next
    Set xg = Intersect(Range("B4:D10"), Target)
    If xg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="111"
    If xg.Value <> str Then xg.Locked = True
    Target.Worksheet.Protect Password:="111"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("B4:D10"), Target) Is Nothing Then
    Set rg = Target.Item(1)
     str = rg.Value
End If
End Sub

Lock Cells after Data Entry in Excel

  • Run the code by pressing the RunSub button or the F5 key.
  • After that, type your data values in the range.
  • It’ll get locked automatically.
  • If you try to edit, you’ll get the warning dialog box as shown below.


6. Unlock Cells in Excel Using VBA

So far, we have explained how to lock cells. Now, you can use the below code to unlock cells in an excel workbook.

Sub UnlockCells()
Sheets("Unlock Cells").Cells.Locked = False
Sheets("Unlock Cells").Unprotect
End Sub

Unlock Cells in Excel Using VBA


How to Lock and Unlock Cells in Excel Without VBA

You can also lock and unlock cells without using VBA. We can use the Format Cells dialog box to lock/unlock cells. You have to check the box for Locked if you want to lock the cells. Similarly, to unlock the cells, unchecking the box will do. Again, locking your cells will have no impact if you don’t protect your worksheet or workbook.

STEPS:

  • First of all, select your desired range.
  • Then, press the Ctrl and 1 keys together.
  • It’ll return the Format Cells dialog box.
  • Under the Protection tab, check/uncheck the Locked box depending upon your necessity.

How to Lock and Unlock Cells in Excel Without VBA

  • If you need to protect the worksheet, go to the Review tab.
  • There, select Protect Sheet from the Protect drop-won.
  • Thus, the sheet will get protected.


Conclusion

Henceforth, you will be able to Lock and Unlock Cells in Excel Using VBA following the above-described examples. Locking certain cells in excel gives you the freedom of securing your data from others. It helps you to prevent accidental deletion or to stop someone else from wrecking your data. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo