Locking cells, columns, and rows in Excel allows you to protect your data. After protecting a worksheet, by default, every cell is locked. It means that they can not be edited. However, 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 of locking and Unlock Cells in Excel Using VBA.
How to Lock and Unlock Cells in Excel Using VBA: 6 Ideal Examples
Excel VBA can perform many complicated tasks very easily. You’ll need to input a code or write one. In this article, we’ll apply VBA to Unlock and Lock 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.
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.
- 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.
- 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
- 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 the Entire Worksheet Except for a 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
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
- 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
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.
- 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 be protected.
Download Practice Workbook
Download the following workbook to practice by yourself.
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 to secure 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.
