Lock the Cell after updating
Excel Tips and Tricks

Lock Excel Cell after Updating

One of our client asked us to create a template wherein data can entered only once. As soon as data is entered in the cell, it should be locked for editing. Only Admin (who has the protection password) should be able to edit this.

Here we have given some solution:

Below is the worksheet wherein data will be entered.

Excel sheet
Excel sheet

 

We have put the VBA code on Worksheet Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.Unprotect "1234"

If VBA.IsEmpty(Target.Value) Then

    Target.Locked = False

Else

    Target.Locked = True

End If

Sheet1.Protect "1234"

End Sub

To open the worksheet change event below are the steps:

  • Open the Visual Basic Editor (Press Alt+F11)
  • Double click on the Sheet available in the Left side
  • Select the Worksheet in the first drop-down
  • Select the Change in the second drop-down
  • Put the above given code here.
Worksheet Change Event
Worksheet Change Event

 

We have also given a button to unprotect the worksheet. It will display an Input box wherein Admin will enter the password to unprotect the worksheet.

Below is the macro code which has been assigned to this button.

Option Explicit

Sub Unprotect_Worksheet()

Dim pwd As String

pwd = InputBox("Please input the password", "Input Password")

If pwd = "" Then Exit Sub

If pwd = "1234" Then

    Sheet1.Unprotect pwd

    MsgBox "Sheet has been unprotected", vbInformation

Else

    MsgBox "Incorrect password"

End If

End Sub

 

Click here to download Excel template.

Watch the step by step video tutorial-

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *