Data validation to prevent deleting cell contents. Ok to change, but not delete.

[9tanstaafl9]

Apparently the Backspace key and the Delete key are not the same. Who knew?

I have a spreadsheet with zeroes in cells that should NOT be deleted. The user needs to be able to change the cell value to a number if they want to, but they cannot enter text, or more importantly, they must not delete the zero.

Is there any way to do this? I had thought I had it covered with a data validation rule, but that only stopped users from using the backspace key. For some reason the delete key works just fine and bypasses my data validation entirely.

Here is the rule I tried to use:

=AND(ISNUMBER(G169),G169<>"")

Any suggestions would be appreciated!

 

[9tanstaafl9]

Almost got it. I realized I could kind of get the effect I needed by simply disabling the Delete key on the keyboard. I found code that will do that (below). It works and is nearly perfect. What would be great would be if I could get it to enter a ZERO instead of just disabling the key. If anyone happens to know how to modify the following code to do that I would appreciate it. I am self taught and this is a bit beyond me.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    Dim rngCell As Range
    If Not Application.Intersect(Target, Me.Range("MonEmpTimes")) Is Nothing Then
        For Each rngCell In Application.Intersect(Target, Me.Range("MonEmpTimes"))
            If Me.Cells(rngCell.Row, "A").Value <> "Pending" Then
                Application.OnKey "{DEL}", ""
                Exit For
            Else
                Application.OnKey "{DEL}"
            End If
        Next rngCell
    Else
        Application.OnKey "{DEL}"
    End If
     
End Sub

 

[9tanstaafl9]

Ha! I figured it out! Myself. Without Google. It's a miracle.

If anyone else has this issue, here is what I did:

I put this code in a regular module:

Private Sub FakeDelete()
Selection.Value = 0
End Sub

And this in the worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    Dim rngCell As Range
    If Not Application.Intersect(Target, Me.Range("MonEmpTimes")) Is Nothing Then
        For Each rngCell In Application.Intersect(Target, Me.Range("MonEmpTimes"))
            If Me.Cells(rngCell.Row, "A").Value <> "Pending" Then
                Application.OnKey "{DEL}", "FakeDelete"
                Exit For
            Else
                Application.OnKey "{DEL}"
            End If
        Next rngCell
    Else
        Application.OnKey "{DEL}"
    End If
     
End Sub

MonEmpTimes is the named range of the cells I don't want actually deleted. Works on dis-contiguous ranges.

I imagine this would work great if you didn't want users to realize that the Delete key had been tampered with. In my case the zeroes are hidden so this works. If you actually wanted to simply delete things you could delete the words FakeDelete in the worksheet sub and you don't need the FakeDelete sub module at all.

Feeling pretty good right now...