Lock Cells using VBA

Today we will learn an important functionality to lock Cells using VBA for editing. We will learn how to lock only the cells that contain formulas to prevent them from being changed. We will also learn to lock the cells for editing once data is entered into them.

So let us begin learning.

Locking Cells and Protect Worksheet Using VBA

To lock a cell, we need to set its locked property to true and then protect the worksheet for editing. When we take a new worksheet, the locked property for all the cells of the worksheet is TRUE by default. The Locked property does not come into effect until we protect the worksheet. Protecting the worksheet enables to prevent the locked cells from being edited.

A cell will not be prevented from being edited until the locked property is TRUE for that cell and the worksheet is protected.

Download the practice workbook to follow along.

We can use the Locked Property with a Range or Cell and set it to True or False as follows.

Range("<raneg>").Locked = <True_or_False>

Thereafter, we use the Protect property of the worksheet to protect it. Password is optional.

Worksheets("<Sheet_Nane>").Protect Password:=<optional_password>

You can use the Unprotect method to unprotect the worksheet and specify the password if the worksheet needs one to be unprotected.

Worksheets("<Sheet_Nane>").Unprotect Password:=<optional_password>

Lock All Cells Of Worksheet

When we take a new worksheet, the locked property for all the cells of the worksheet is True by default. So we only need to Protect the worksheet to prevent all the cells to edit. You can run the following Sub Procedure to Protect all worksheets of Active Workbook.

Sub LockAllCellsOfAllWorksheets()
'lock all the cells of all worksheets
Dim ws As Worksheet
For Each ws In Worksheets
  ws.Protect
Next ws
End Sub

Lock Cells Containing Formulas

When we wish to share the excel workbook containing formulas but do not want anyone to mess with the formulas in our workbook, then we can use this method to protect all the cells to edit that contain formulas.

You can use the following Sub Procedure so all the cells of all your worksheets of the active workbook would not be edited until the password is entered to unprotect the worksheets.

Sub lockCellsContainingFormulas()
'lock all the cells containing formulas
Dim ws As Worksheet
'loop through all worksheets
For Each ws In Worksheets
    With ws
    'Unprotec the worksheet if it is protected
    .Unprotect Password:="PasswordToUnprotect"
    'unlock all the cells of worksheet
    .Cells.Locked = False
    'lock the cells containing formulas
    .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
    'protect the worksheet with a password
    .Protect Password:="PasswordToUnprotect"
    End With
Next ws
End Sub

Follow these steps to implement this Sub Procedure for your workbook that you want to protect and lock for editing.

• Open the workbook that has worksheets containing formulas.
• Press Alt and F11 keys to open Visual Basic Editor.
• Select the Project for your workbook in the Project Explorer Pane at the Left.
• Go to the Insert tab and Insert a New Module.
• Paste the Above Code in the new Module1.
• With your cursor in the Sub Procedure code, press F5 to run it for active Workbook.

This is how we lock cells using VBA.

However, you can Unprotect the worksheets by using the following Sub Procedure.

Sub unprotectAllWorksheets()
Dim ws As Worksheet
For Each ws in Worksheets
  ws.Unprotect Password:="PasswordToUnprotect"
Next ws
End Sub

Lock Cells From Editing After Data is Entered into It

We can lock the cells from being edited right after the data is entered into the cell and the Enter key is pressed. This enables the user to enter data in empty cells of the worksheet and once the data is entered, the cells are automatically locked for editing.

We are using the Worksheet Change Event Handler. The code executes automatically by Excel when someone enters data in any worksheet cell.

This time, we are doing this for the cells of one worksheet. Follow these steps.

• Right, Click on the Sheet Tab that you wish to lock for editing once data entered into the cells.
• Choose the option View Code. Paste this code to the code window and then close the Visual Basic Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
'check if all the cells are locked and this is a new worksheet
If Cells.Locked = True Then
    'unlock all the cells as they do not contain any data
    Cells.Locked = False
End If
'unprotect the worksheet
ActiveSheet.Unprotect Password:="OurPassword"
'lock the cell in which data is entered
Target.Locked = True

ActiveSheet.Protect Password:="OurPassword"
End Sub

Now that you enter data in any cell, you won’t be able to delete it or change it until you unprotect the worksheet with the password that you used in the above sub-procedure.

This brings us to an end.

Thank you for reading. ❤