Automatically Password Protect Excel Worksheet and Lock Cells after Data Entry using VBA


How to automatically password protect Excel Worksheet and lock cells after data entry using VBA. A user has the following query:
I am an accounts officer. I have designed an automated template for collection of school fees on daily basis. I have realized that, at the end of the day, the person who does the data entry for the collection of school fees changes or decreases the amount paid by the students, which causes a lot of problems in the school. I want a situation whereby after a user has entered data under the heading ‘Fees’, the cells into which data has been entered is locked and password protected.

We know how to make the data entry operator confirm her data entry into an Excel worksheet. Once the data has been entered we wish to lock the cells in such a way that the data entry operator requires a password to make any changes. Watch the training video below before studying the complete VBA code:


Download a sample Excel file:


The complete VBA code to password protect and lock Excel worksheet cells for Sheet1:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 2 Then
confirm = MsgBox(“Do you wish to confirm entry of this data?” _
& vbCrLf & “You will not be allowed to change it!”, vbYesNo, “confirm Entry”)
Select Case confirm
Case Is = vbYes
Dim Cell As Range
With ActiveSheet
.Unprotect Password:=”asdf,1234″
.Cells.Locked = False
For Each Cell In ActiveSheet.UsedRange
If Cell.Value = “” Then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
.Protect Password:=”asdf,1234″
End With
Case Is = vbNo
Application.Undo
End Select
End If
Application.EnableEvents = True
End Sub

Further reading:
Events And Event Procedures In VBA
Spreadsheet Protection FAQ