Home>Blogs>Excel Tips and Tricks>Lock Excel Cell after Updating
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


    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


    MsgBox "Incorrect password"

End If

End Sub


Click here to download Excel template.

Watch the step by step video tutorial-

Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!

Leave a Reply