Delete is Pressed then Stop

[Lenny_821]

Hi All,

I am looking for a VBA code that if the delete button is pressed on a
particular cell (B1 - B100), on a worksheet, that a code will stop it.

Something like this;
Private Sub Worksheet_*BeforeDelete(*ByVal target As Excel.Range)
msgbox("cell can not be deleted")
end sub


Please Help

Lenny

 

[Tom Ogilvy]

Right click on the sheet tab and select view code. Put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo ErrHandler
Set rng = Intersect(Range("B1:B100"), Target)
If rng Is Nothing Then Exit Sub
If (rng.Count > 1 And Application.CountA(rng) = 0) Or (rng.Count = 1 _
And IsEmpty(rng)) Then
Application.EnableEvents = False
Application.Undo
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Drawback: If a multicell area is selected that overlaps B1:B100 and the
user hits delete, none of the cells will be deleted.