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