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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Read More: How to Protect Certain Cells in Excel (2 Suitable Ways)
Similar Readings
- How to Unlock Cells in Excel When Scrolling (4 Easy Ways)
- Excel VBA to Lock Cells without Protecting Sheet (4 Ideal Examples)
- Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking
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.
Read More: How to Protect Excel Cells from Being Edited (6 Suitable Methods)
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.
Read More: How to Protect Excel Cells with Formulas (2 Simple Methods)
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.