Protect After Saved

[Freshman]

Dear experts,

I've a worksheet where a range A5100 is for some users to fill in
information. Is there a way when the cell is blank, it can be input by any
users. However, whenver an user fill in some information to some rows within
the range, the non-blank cells will be protected by a password, say "tiptoe"
after saved but other blank cells will still be unprotected and can be input
by other users. Is there a VBA code can do this? If yes, please kindly advise
the code.

Thanks in advance.

 

[Gord Dibben]

Select A5100 and set properties to "unlocked". All other cells set to
"locked"

Right-click on the sheet tab and "View Code". Copy/paste this into the module
that opens up.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A5100")) Is Nothing Then
ActiveSheet.Unprotect Password:="tiptoe"
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="tiptoe"
End Sub

Alt + q to return to the Excel sheet.

As you enter data in A5100 the cells will become locked.


Gord Dibben MS Excel MVP

 

[Freshman]

Dear Gord,

Thanks for your time to write me the code. I'm really appreciated it. Have a
nice weekend.

Best regard to you and your family.

 

[Gord Dibben]

Thanks for the feedback and we will have a nice weekend.


Gord