Forum Discussion

Yeshika44's avatar
Yeshika44
Copper Contributor
Aug 30, 2024
Solved

How to apply 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 (R4:R1000 and U4:U1000). My question is, if we enter “Open” in cell range R, then unlock the specific cell in column “U” and open drop down with Yes & No. If we have “Close” in cell range “R”, then specific cell in column “U” should restrict the value entry and move the cursor to column “V”. Basically, column “U” value is based on Column “R”. This should apply throughout whole range (R4:R1000 and U4:U1000).

 

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

 

Thank you very much.

Yeshi

excel
Macros and VBA
  • m_tarler's avatar
    m_tarler
    Aug 31, 2024

    Yeshika44  here is an idea.  add this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Locked Then
        If Target.Column = 21 Then
            MsgBox ("This cell is locked because this row is not ""Open"" ")
        Else
            MsgBox ("No data entry needed here")
        End If
        Cells(Target.Row, 18).Select
    End If
    
End Sub

    it will give that prompt and then force the cursor back to the cell in column R.  If you prefer you could tweak it to another location 

    I also added a msgbox if they pick a cell in column U that is locked

     

8 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Aug 30, 2024

    Yeshika44  so you can use this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    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 = "Open" Then
            uCell.Locked = False
        Else
            uCell.Locked = True
        End If
        ActiveSheet.Protect
    End If
End Sub

    but make sure you select the cells in column R go to properties and unlock them 

    Also, the "Open" is case sensitive so you might want to apply UPPERCASE and compare to "OPEN" or such

    I used ActiveSheet.Range("U1").Column to make it read easy that you are going to lock/unlock column U but you could replace that with 21

     

    • Yeshika44's avatar
      Yeshika44
      Copper Contributor
      Aug 31, 2024

      Hello m_tarler 

       

      Thank you very much and my sincere thanks go out to you for your prompt response. It is working perfectly fine, however I have few concerns:

      • I am wondering if there is another way to do this without protecting the sheet.
      • In the current version, it displays an error alert (as attached) when we attempt to enter data in restricted cells. Is there a way to change/customise the error alert to appear in a way that users can understand more easily E.g like “In this section, no data entry is required, Please move to column “W”?

       

       

      If you can assist me with this would be much appreciated

      Many thanks

      Yeshi

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Aug 31, 2024

        Yeshika44  here is an idea.  add this:

        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Locked Then
        If Target.Column = 21 Then
            MsgBox ("This cell is locked because this row is not ""Open"" ")
        Else
            MsgBox ("No data entry needed here")
        End If
        Cells(Target.Row, 18).Select
    End If
    
End Sub

        it will give that prompt and then force the cursor back to the cell in column R.  If you prefer you could tweak it to another location 

        I also added a msgbox if they pick a cell in column U that is locked

         

Resources