Hi,
I'm trying to write some code that detects whether someone has deleted data
using the delete key, on a particular sheet.
I use the following to catch data in the active cell before it is deleted,
and store it in AB2:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not IsEmpty(ActiveCell) Then Range("AB2") = ActiveCell.Value
End Sub

I don't mind if data is added or changed - I just want to know if it's been
deleted.
The delete-detect macro would display a message box telling the user that
they've been naughty, then use the value of AB2 to reinstate the deleted
data. This part I can do. I just can't seem to get the detect-delete part.

I have tried the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(ActiveCell) Then MsgBox _
"No manual deletions allowed on this page" _
& Chr(10) & "Use the Delete button in Cell H1"
End Sub

This displays the msgbox when I delete something, but it also displays when
I add data to a cell, then hit enter. Any way of doing what I want?

Any help would be appreciated.
Regards - Dave.

On Apr 27, 7:24*pm, Dave <D...@discussions.microsoft.com> wrote:
> Hi,
> I'm trying to write some code that detects whether someone has deleted data
> using the delete key, on a particular sheet.
> I use the following to catch data in the active cell before it is deleted,
> and store it in AB2:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not IsEmpty(ActiveCell) Then Range("AB2") = ActiveCell.Value
> End Sub
>
> I don't mind if data is added or changed - I just want to know if it's been
> deleted.
> The delete-detect macro would display a message box telling the user that
> they've been naughty, then use the value of AB2 to reinstate the deleted
> data. This part I can do. I just can't seem to get the detect-delete part.
>
> I have tried the following:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If IsEmpty(ActiveCell) Then MsgBox _
> "No manual deletions allowed on this page" _
> & Chr(10) & "Use the Delete button in Cell H1"
> End Sub
>
> This displays the msgbox when I delete something, but it also displays when
> I add data to a cell, then hit enter. Any way of doing what I want?
>
> Any help would be appreciated.
> Regards - Dave.

Dave,

You need to change the If IsEmpty(ActiveCell) to If IsEmpty(Target)

Cheers

Alex

Hi Alex,
Thanks a lot! Works a treat
Regards - Dave, Kiwi in Brazil
>
> You need to change the If IsEmpty(ActiveCell) to If IsEmpty(Target)
>
> Cheers
>
> Alex
>