Custom data validation that locks the cell as per the value in the other cell.

[Yeshika44]

Hi there

My question is related to the custom data validation that locks the cell as per the value in the other cell.

Hi there

My question is related to the custom data validation that locks the cell as per the value in the other cell.

I have two data columns (G1:G1000 and K1:K1000). My question is, if we enter “Open” in cell range G, then unlock the specific cell in column “K” and open drop down with Yes & No. If we have “Close” in cell range “G”, then specific cell in column “K” should restrict the value entry and move the cursor to column “L”. Basically, column “J” value is based on Column “K”. This should apply throughout whole range (G1:G1000 and G4:U1000).

I tried following code, but it doesn’t work, I would greatly appreciate if anyone can help.


Spreadsheet is attached.

Thank you very much.

 

[vletm]

Yeshika44​

Have You tested to Unprotect sheet before add validation?
... or do Your sheet has needed Protection settings to add it?
Have You tried to use UserInterfaceOnly -option, instead of swapping between Protect & Unprotect?

 

[ExcelGoogler]

@Yeshika44 ,

May I ask why? Even if you do get this running the way you describe, it essentially does nothing more than leave the sheet protection in a random state that is dependent on the data provided last. How would this provide any benefit beyond the current functionality of Excel?

Data validation should be in place and used as it's encountered. Dynamically adding the Yes/No doesn't serve any purpose and has no function other than ensuring that the value in column G is only a "Yes" or a "No". No other "validation" takes place.
Sheet protection should be set with the purpose of avoiding accidental changes. Just leave columns K & G unlocked if that is all that is changing.

Given the high level of skill sets here that are willing to help, I am sure if you provide the story or need you have that someone will be able to find a solution.

Cheers!

 

[Yeshika44]

I appreciate all your efforts to assist me, however, another legend helped me with this, so I wanted to share it here in case someone else needs similar assistance.

>>> use code - tags <<<

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    If Not (Application.Intersect(Range("V3:V2000"), Target) Is Nothing) Then _
    ThisWorkbook.Sheets(Target.Value).Activate
    
    If Not Intersect(Target, Range("R4:R1000")) Is Nothing Then
        ActiveSheet.Unprotect
        Set uCell = ActiveSheet.Cells(Target.Row, ActiveSheet.Range("U1").Column)
        If Target.Value2 = "Transfer" Then
            uCell.Locked = False
        Else
            uCell.Locked = True
        End If
        ActiveSheet.Protect
        
        ActiveSheet.Unprotect
        Set uCell = ActiveSheet.Cells(Target.Row, ActiveSheet.Range("V1").Column)
        If Target.Value2 = "Transfer" Then
            uCell.Locked = False
        Else
            uCell.Locked = True
        End If
        ActiveSheet.Protect
    End If
End Sub

 

[vletm]

Yeshika44​

Did You read any of Your used Forums Rules before posting?
All of those has basically same rules ..

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

These rules are for everyone.