Improve company productivity with a Business Account.Sign Up

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

Locking/Unlocking Cells Based on Value in Another Cell

I need to unlock a cell based on whether or not another cell in the first cell's row contains a specific value.
Attached is a sample workbook where I need to evaluate range C6:C23 and see if any cells in that range contains (not necessarily equal) the value in cell E3 and if it does then the cells 2 and 3 columns to the right are unlocked for editing.

I would like this code to execute when the worksheet is activated.

Thanks,

Edwin
TestPage001.xlsm
0
gixxer1020
Asked:
gixxer1020
1 Solution
 
Rgonzo1971Commented:
Hi,

Pls insert this code in the worksheet module
Private Sub Worksheet_Activate()
    strPassword = "" ' "pw"
    ActiveSheet.Unprotect Password:=strPassword
    ActiveSheet.Range(Range("C6"), Range("C" & Rows.Count)).Offset(, 2).Resize(, 3).Locked = True
    For Each c In ActiveSheet.Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
        If c.Value Like "*" & Range("E3").Value & "*" Then
            c.Offset(, 2).Resize(, 3).Locked = False
        End If

    Next
    ActiveSheet.Protect Password:=strPassword, UserInterfaceOnly:=True
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
I have got round similar issues by using custom Data Validation.

You can use a formula to validate an entry into a cell.

When the check on the other cell is verified to disable entry (Lock cell) set the validation rule such that the entry is impossible to be valid eg if numerical entry >0 AND <0, the entry can't be both so will be rejected; if text "contains 200 z's" although not impossible is unlikely.

When the check on the other cell is verified and will allow entry then you can set the validation to be anything.

Thanks
Rob H
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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