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


--
Lenny_821
------------------------------------------------------------------------
Lenny_821's Profile: http://www.excelforum.com/member.php...o&userid=15179
View this thread: http://www.excelforum.com/showthread...hreadid=268122

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.

--
Regards,
Tom Ogilvy


"Lenny_821" wrote in message
...

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


--
Lenny_821
------------------------------------------------------------------------
Lenny_821's Profile:
http://www.excelforum.com/member.php...o&userid=15179
View this thread: http://www.excelforum.com/showthread...hreadid=268122