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.

Excel VBA to Protect Range of Cells: 3 Examples

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 shows that the 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.

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 contain 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.

Download Practice Workbook

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

Protect Range of Cells.xlsm

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 give your suggestions in the comment box.

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Tom Oct 14, 2023 at 8:39 PM

Hi,
I am wanting to make a log (using Excel) for Shift Supervisors that they can password lock their entries. Can more than 4 individuals use their own password to lock their range of cells using VBA?
- Reply
  Alok Paul Oct 15, 2023 at 4:51 PM
  Dear Tom,
  Thanks for reading our articles. Here, you queried that you want an Excel sheet based on VBA to input values for 4 users in separate specified range. Those cells are locked with different passwords for each user.
  Here, we have set User 1 with range B5:C10 and password: passuser1, User 2 with range E5:F10 and password: passuser2, User 3 with range H5:I10 and password: passuser3, User 4 with range K5:L10 and password: passuser4.
  
  Based on the above information our VBA code will be as follow.
```
Sub Protect_Multiple_Range_with_Password_for_Specific_User()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'change accordingly

Dim i As Integer
For i = ws.Protection.AllowEditRanges.Count To 1 Step -1
    ws.Protection.AllowEditRanges(i).Delete
Next i

' Define the ranges and passwords
    rangeAddresses = Array("B5:C10", "E5:F10", "H5:I10", "K5:L10") ' Add or modify ranges as needed
    passwords = Array("passuser1", "passuser2", "passuser3", "passuser4") ' Add or modify passwords as needed

'Unlocking Sheet if already locked
If ws.ProtectContents = True Then
ws.Unprotect Password:=1234 'change accordingly
End If

    For i = LBound(rangeAddresses) To UBound(rangeAddresses)
        ws.Protection.AllowEditRanges.Add Title:="Range_" & CStr(i + 1), Range:=ws.Range(rangeAddresses(i)), Password:=passwords(i)
        ws.Range(rangeAddresses(i)).Locked = True
    Next i
 MsgBox "Multiple ranges have been set for different users with different passwords using AllowEditRanges feature.", vbInformation
 
 'again Locking the sheet with master password 1234
ws.Protect Password:=1234, DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
```
  • When you double-click on any cell specified for any user a window named Unlock Range will pop-up. Insert the password and click on OK to edit the range of the user.
  
  • But if you click on any cell that is not associated with any user then you will get a warning.
  
  We can also unprotect the whole using password: 1234.
  
  Regards
  Alok

Excel VBA to Protect Range of Cells (3 Examples)

Excel VBA to Protect Range of Cells: 3 Examples

1. Protect a Certain Range of Cells

2. Protect Cells with Password from Selection

3. Detect Cells with Formulas and Protect them

Conclusion

Related Articles

Leave a reply Cancel reply