Protecting a CELL but allow drop down options

Question

I want to protect a cell but also allow a user to select options from the drop down list ?

I read the below question which does not provide an answer.

Protecting a cell, but allowing the drop down list to work

Un-protecting the cell is not what I want to do, as a user could simply paste over the cell and the data validation is then gone.

Is there a way to have a locked CELL and have sheet protection on but still allow data validation list options to be selected ?

I will try any options including VBA.

Answer 1

There might be a better way of doing it, but I had an idea and it seems to work fine with my testing so far.

So I use Worksheet_SelectionChange to monitor what the user is doing with the list.
I simply unlock the sheet when the cell is selected, and lock it again when something else is selected:
Note that my list is in N26

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("N26")) Is Nothing Then
    ActiveSheet.Unprotect
Else
    ActiveSheet.Protect
End If
End Sub

The problem with this is obviously that you should be able to select the cell, and then paste whatever.

So we need to check if the user is pasting something, and I found some interesting stuff over here: https://stackoverflow.com/questions/27818152/excel-vba-how-to-detect-if-something-was-pasted-in-a-worksheet

So with this we can check if something has been pasted, and undo it.

I put them together, modified it, and so the user can paste in any unlocked cells, but not in N26.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("N26")) Is Nothing Then 'Data validation list adress
    ActiveSheet.Unprotect
Else
    ActiveSheet.Protect
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("N26")) Is Nothing Then
  Dim UndoList As String
  '~~> Get the undo List to capture the last action performed by user
  UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

  '~~> Check if the last action was not a paste nor an autofill
  If Left(UndoList, 5) = "Paste" Then
        With Application
            .EnableEvents = False
            .Undo                  'Undo paste
            .EnableEvents = True
        End With
  End If
End If
End Sub

Note that some of these values may change if you aren't using an english version of Excel. I had to change "&Undo"and (UndoList, 5) = "Paste"to my language.

I guess you could also use this for your next question. If you allow paste again, but then check every change for paste, and if true, run IsNumeric(Target.value) check and undo if it fails.