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.

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

Table of Contents

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.

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