Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

Excel Conditional Cell Protection

I have a spreadsheet with rows which users need to edit before final approval but not afterward.  I need some help with conditional protection.  Basically, in the attached spreadsheet, if L5 is <> null, I need entire row 5 protected.  If L5 = null, then the row should be open for editing.  And then this same criteria applied to the remainder of the spreadsheet, with the L column from cell 5 onward as the key.  Can anyone help?
PropertyPass.xls
0
mrherndon
Asked:
mrherndon
  • 4
  • 3
1 Solution
 
TracyVBA DeveloperCommented:
Your sheet is password protected.  Unprotect and re-post.
0
 
mrherndonAuthor Commented:
Apologies.  Unprotected.
PropertyPass.xls
0
 
TracyVBA DeveloperCommented:
OK, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler

    Application.EnableEvents = False

    If Target.Column = 12 And Target.Count = 1 Then
        Call UnProtect
        If Target.Value <> "" Then Rows(Target.Row).Locked = True
        Call Protect
    Else
    End If

finished:
    Application.EnableEvents = True
    Exit Sub

errHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume finished

End Sub

Open in new window

PropertyPass-2-.xls
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mrherndonAuthor Commented:
Thanks.  It works great on the "test" line.  But the rest of the sheet is unaffected.  What am I missing?
0
 
TracyVBA DeveloperCommented:
The values are already filled in.  This code is triggered when you enter a new value in column L.  So either protect the cells now for each line that already has a value, or remove the initials and then re-add them back in and the code will execute for each one.
0
 
mrherndonAuthor Commented:
I see.  So, going forward it will lock new data.  Existing data I can manually lock.  Do I understand you correctly?
0
 
TracyVBA DeveloperCommented:
Yes, that's correct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now