Prevent data being deleted by using "DEL" key in a protected spreadsheet

_123 · Mar 6th 2011

Hi all,

I have created a protected spreadsheet for work - I have managed to stop the cut/paste function however I am struggling to figure out how to stop them just hitting the "Delete" button on the keyboard and removing everything.

Essentially - there are a few sections in the spreadsheet and each section has an "Approved" yes/no/pending status. I only want people to be able to edit/delete when the status is "pending". I have managed to do this using Data Validation....however they can still just hit the "Delete" key and everything is gone.

Any help?? Basically when Approved = either "Yes" or "No" I want to disable the Delete key.

Any ideas?

Thanks

**S M C** · Mar 6th 2011

Re: Prevent data being deleted by using "DEL" key in a protected spreadsheet

You could use something like this.

Code

Application.OnKey "{DEL}",""

An example for your case would be as below for example, where Range B1:B10 is your range of concern, and A1:A10 is your Approved status section

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    Dim rngCell As Range
    If Not Application.Intersect(Target, Me.Range("B1:B10")) Is Nothing Then
        For Each rngCell In Application.Intersect(Target, Me.Range("B1:B10"))
            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

Display More

_123 · Mar 7th 2011

Re: Prevent data being deleted by using "DEL" key in a protected spreadsheet

Thanks! that worked really well!

Although I have just discovered another issue...

Even with the Data Validation rule - when I hit "Backspace" that still removes the data from the cell....I don't think that should be the issue and I have no idea why that's happening.

Any ideas?

Thanks

Prevent data being deleted by using "DEL" key in a protected spreadsheet

Participate now!

Share