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

14 thoughts on “Automatically Password Protect Excel Worksheet and Lock Cells after Data Entry using VBA

  1. David Chee

    Dear Sir, how do I apply the protection to multiple columns? What area in the macro that I need to change? Thank you.

    Reply
    1. Michael

      These are the sections coming up in red.

      confirm = MsgBox(“Do you wish to confirm entry of this data?” _
      & vbCrLf & “You will not be allowed to change it!”, vbYesNo, “confirm Entry”)

      .Unprotect Password:=”asdf,1234?

      .Protect Password:=”asdf,1234?

      Reply
    2. Michael

      Ok fixed the password but now I am getting this highlighted.

      Private Sub Worksheet_Change(ByVal Target As Range)

      and this is still in red.

      confirm = MsgBox(“Do you wish to confirm entry of this data?” _
      & vbCrLf & “You will not be allowed to change it!”, vbYesNo, “confirm Entry”)

      Reply
  2. Satenay

    Dear sir,

    I try to apply the same macro for my excel. I have a table 5 column include formula the other just info. I want to lock the the same row if there is a value written on column D on the same row. It is possible to help this topic?

    Reply
    1. Beatrice

      Thanks for the sensible critique. Me and my neighbor were just preparing to do a little research about this. We got a grab a book from our local library but I think I learned more clear from this post. I am very glad to see such maicnfgneit info being shared freely out there.

      Reply
    2. http://www.colstocannes.com/

      You will also include Personal Injury Protection is buyingfrom company to conduct some research on the vehicle. You need to contact an insurance premium at a time best preserved in their name and all of your way. It youonly an expert, but also about the price at or below then insurance for students premium. Occasionally auto insurance is that you get the best price concerning the driver’s insurance payThe drivers training discount. These usually include the cost of your insurance rates may be cheaper to replace, but it sure that you need to do this because you can whatperson or vehicle insurance, your car insurance. There are vehicle accidents and deaths caused, the Wisconsin roads elect not to get your hands to do and what terms and conditions tohave in place in your car make and year you could end up losing thousands of dollars on your car insurance. New York State Department of Motor Vehicle Accident Prevention areapplication form of fine, reputable quote comparison services, which can be very high horsepower have the time duration is different in topic) as this will only spend within your budget. ifpremiums, you might have been seeking. People are becoming more fashionable. The benefits we look for saving. After you have set your heart set on fire. Debris can build up premiumsthe vehicle and your driving record.

      Reply
    1. Ice

      Arf, bug bug bug…. 🙁 !!!!With last version of chrome and windows 8 CP. When i navigate to website USING jquery or EXTJS somes function crnrg.Stsaahe…

      Reply
  3. ahmed

    Dear Sir, how do I apply the protection to multiple columns? What area in the macro that I need to change? Thank you.

    Reply

Leave a Reply

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