How to prevent someone from deleting cell content VB code

[pulsarkuant]

I have the code below securing and preventing someone from deleting cell content in cell C9 only. How can I use the same code to protect Columns A2, B2, C2, D2, E2 and F2 and 2500 rows? I tried making A:F and a few other combinations, but it didn't work. Please note: I still want users to edit or enter new data.

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

 

[rlv01]

Assuming that the code you posted about works the way you want it to now (and I have no idea if it does or not), then you could extend it in this way.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim IgnoreRange As Range
    
    With Me
        Set IgnoreRange = .Range("A2:F2500")  '<- *you* must define this range.
    End With
    
    If not Application.Intersect(Target, IgnoreRange) Is Nothing 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

 

[pulsarkuant]

Thank you, rlv01. Yes, the code I gave initially works fine. The code you gave appears to take no action, unfortunately. I can still delete the entries.

 

[rlv01]

Thank you, rlv01. Yes, the code I gave initially works fine. The code you gave appears to take no action, unfortunately. I can still delete the entries.

If you did not follow the instructions to define your ignore range then I expect you would have problems.

Set IgnoreRange = .Range("A2:F2500")  '<- *you* must define this range.

 

[pulsarkuant]

If you did not follow the instructions to define your ignore range then I expect you would have problems.

Set IgnoreRange = .Range("A2:F2500")  '<- *you* must define this range.

You are 100% right. Initially, I thought the range was for defining the affected zone. That works thank you sir.

 

[pulsarkuant]

I just noticed if the user selects a range of cells and then hits the delete key on the keyboard, this code does not protect and allows the record's deletion. It works only if the user selects and attempts to delete a single cell. How can I fix this?

 

[rlv01]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim IgnoreRange As Range, R As Range
    
    With Me
        Set IgnoreRange = .Range("C1:C50")            '<- *you* must define this range.
    End With
    
    For Each R In Target
        If Application.Intersect(R, IgnoreRange) Is Nothing Then
        If Len(R.Value) = 0 Then
            Application.EnableEvents = False
            Application.Undo
            MsgBox "You can't Delete,,,!"
            Application.EnableEvents = True
            Exit For
        End If
        End If
    Next R
End Sub

 

[pulsarkuant]

Legend! Thank you sir.