How to get error only when edited or when double clicked.

[DemonX]

Following code was provided by a good samaritan in this forum, and while I do testing, I found an error which I kindly need further assistance.

Below code will give an error when the excel cell is double-clicked when it is locked in format. Now the issue is, I observed when the cell is just selected ( only one click on cell), users are still able to edit ( enter values ).

I can not use Protect sheet functionality since it will ignore customize error .

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Locked Then
MsgBox "Stop!" & vbNewLine & vbNewLine & "Please Update only the YELLOW cells.", vbCritical, "STOP!"
Cancel = True
End If
End Sub

Could we get an error when double-clicked or when any value tried to enter? At least the latter would be great.

 

[jasonb75]

I can not use Protect sheet functionality since it will ignore customize error .

It would be better to protect the sheet and sacrifice the custom message. There a far too many ways that using vba in an attempt to protect an unprotected sheet can fail.

Your existing code doesn't work if you click the OK button then start typing.

 

[Fluff]

I agree with Jason.
If you have the entire sheet locked, but unlock cells A1:A3 then enter any value into those 3 cells & then enter something in A4 it will unlock itself.