Automatically lock cells after inputting data

Anonymous
2014-05-09T13:34:23+00:00

If a user enters the information in a certain cell, I want that cell to lock. I am not always around to protect and un-protect the worksheet so I need to figure out a way to make this possible. Any ideas?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2014-05-09T13:53:06+00:00

Hi,

Right click the sheet tab, view code and paste this code in on the right. Change the range to the correct one and the password to the correct one.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Range, MyRange As Range

Set MyRange = Range("A1:A1000")

If Not Intersect(Target, MyRange) Is Nothing Then

ActiveSheet.Unprotect Password:="secret"

For Each c In Intersect(Target, MyRange)

c.Locked = True

Next

ActiveSheet.Protect Password:="secret"

End If

End Sub

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more