Excel VBA to Protect Range of Cells (3 Examples)

In an Excel worksheet, we enter data in different cells. Of them, some cells contain information those are unchangeable or may contain formula, etc. As those cells are sensitive, we need to protect them. We can protect cells in different ways. But in this article, we will show some examples of Excel VBA to protect a range of cells.

Table of Contents hide

Download Practice Workbook

3 VBA Codes to Protect Range of Cells in Excel

1. Protect a Certain Range of Cells

2. Protect Cells with Password from Selection

3. Detect Cells with Formulas and Protect them

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Protect Range of Cells.xlsm

3 VBA Codes to Protect Range of Cells in Excel

We will show 3 VBA examples to protect a range of cells in different situations. We have a dataset that contains formulas in the Savings column, and the rest are only values.

1. Protect a Certain Range of Cells

In this example, we show how to protect the whole dataset. We will put the data range in the VBA code. When we run the code those cells will be in protected mode.

Steps:

Now, go to the Sheet Name We will get Sheet Name at the bottom bar of any sheet.
Press the right button of the mouse.
Choose the View Code option from the Context Menu.

We enter the VBA window. Choose Module from the Insert tab.

VBA module window appears now. We will write our VBA code here.

Copy and paste the following VBA code on the module.

Sub Protect_Range_Cells()
Dim range_1 As Range
Set range_1 = Range("B4:E8")
Cells.Select
Selection.Locked = False
range_1.Select
Selection.Locked = True
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
End Sub

Press F5 to run the VBA code.

Now, click on any of the cells of the inputted range.

A warning is showing that that cell is in protected mode. But we can edit or insert data on other cells.

Code Breakdown:

Dim range_1 As Range

Declare a variable of the range type.

Set range_1 = Range("B4:E8")

Set the value of the variable.

Selection.Locked = False

Unlocks the selected range. The False comment unlocks the range.

range_1.Select
Selection.Locked = True

Selects range_1 and locks the selection.

Similar Readings

2. Protect Cells with Password from Selection

In this example, we will protect a range of cells from selection with a password.

Steps:

First, select the cells of the Cost and Savings columns of the worksheet.
Then, go to the VBA module and write down the following VBA code.
Finally, press the F5 button to run the code.

Sub Protect_Range_With_Pass()
 Dim pass_1 As String
 pass_1 = "Exceldemy"
 If ActiveSheet.ProtectContents = True Then
   ActiveSheet.Unprotect Password:=pass_1
 End If
 ActiveSheet.Cells.Locked = False
 Selection.Locked = True
 ActiveSheet.Protect Password:=pass_1
End Sub

Now, click any cells of the Cost or Savings columns.

Like the previous, a warning will show. If we put the password, the cells will be in unprotected mode.

To unlock the cells click on the Unprotect Sheet option from the Review tab.
The Unprotect Sheet window will appear.
Insert the password on the Password box and then press OK.

Code Breakdown:

Dim pass_1 As String

Declare the variable of string type.

pass_1 = "Exceldemy"

Set a value of the variable.

If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect Password:=pass_1
End If

An If condition is applied. Set the value of the pass_1 variable as the password.

ActiveSheet.Cells.Locked = False

Unlocks the active cells.

Selection.Locked = True

Locks the selected cells.

 ActiveSheet.Protect Password:=pass_1

Set pass_1 as the password of the cells of the present sheet.

3. Detect Cells with Formulas and Protect them

We have formulas in the Savings column. We will protect those cells by running a VBA code in this example.

Formulas are shown in the adjacent cells of the corresponding Savings column.

Steps:

Copy the VBA code below and paste it into the VBA module.

Sub Protect_Formula_Cells()
For Each cell In ActiveSheet.Range("B4:E8")
If cell.HasFormula Then
cell.Locked = True
Else
cell.Locked = False
End If
Next cell
ActiveSheet.Protect "abcd"
End Sub

Hit the F5 button to run the code.
Then, click any cells of the Savings column that contains formulas.

We see a warning dialog box like in the previous examples.

Code Breakdown:

For Each cell In ActiveSheet.Range("B4:E8")

A for loop is applied on the range of the present sheet.

If cell.HasFormula Then
cell.Locked = True
Else
cell.Locked = False
End If

An If condition applied. If our selected range has any formula then those cells will be locked. And the rest of the cells will be unlocked.

ActiveSheet.Protect "abcd"

The active sheet is protected by a password.

Conclusion

In this article, we showed VBA examples to protect a range of cells in Excel. We can protect any number of cells with different criteria by following the examples. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.