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

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...
- m_tarler
  Bronze Contributor
  Sep 01, 2024
  not sure why, that is weird but most importantly, glad it is working for you.
  - Yeshika44
    Copper Contributor
    Sep 01, 2024
    Dear m_tarler , I've attached a spreadsheet I am working if its helpful to figure out this. Your help on this would be very much appreciated.
    Many thanks
Yeshika44
Copper Contributor
Sep 01, 2024
YOU ARE AN ABSOLUTE LEGEND! You have provided me with great assistance, and I would like to thank you again for your prompt responses. This is working perfectly fine for me. I cannot adequately express my gratitude… However, thanks again and I greatly, greatly appreciate the assistance you have provided.