Excel allowing deletion of cell contents of data validated cell

I have a worksheet where only cells which should be edited are unlocked, and the whole worksheet is then protected. One of the cells where editing is permitted has data validation set, based on a 2 item list. Data validation is setup to allow a List and the appropriate range for the Source.

The validation works to a point, in that choosing one of the two options in the dropdown is accepted, and entering a value not contained in the list is declined.

The problem I'm having is that Excel is allowing me to delete the contents of said cell (which I do not want - I want it to always have one of the two values in the list). Within the data validation window, I have tried both checking and unchecking the Ignore blank checkbox, which doesn't seem to have any effect.

Ideally, I'd like to do this without the need for VBA, but will concede if it is the only way.

Here is a link to an example workbook - for Option4x, you are permitted to choose either of the dropdown options, but not permitted to enter random text. However, you are also permitted to delete the contents of the cell (which I don't want).

Solution 1:

Data Validation only checks whether the entered data is as per the rules/list or not. Since the Cell is editable that's the reason Excel allows you to Delete the entered data but not allowing to write out of the list.

If you don't allow the validated data to be deleted then you need to Disable the Delete key. The below written sample code will help you to prevent the exercises.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$C$9" Then Exit Sub
   If Len(Target.Value) = 0 Then
        Application.EnableEvents = False
          Application.Undo
        MsgBox "You can't Delete,,,!"
     Application.EnableEvents = True
   End If

End Sub

NB: Since Excel don't have any function could prevent the cell, in this case only VBA will work.