Forum Discussion

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 “O...

excel

Macros and VBA

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

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
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
  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
  - Yeshika44
    Copper Contributor
    Sep 01, 2024
    Dear Mr.m_tarler , I am really sorry, I dont know why I cant put "likes" here, its not accepting... Not sure because I am a new user...