Detect Delete

[Dave]

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.

 

[Alex Simmons]

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

 

[Dave]

Hi Alex,
Thanks a lot! Works a treat
Regards - Dave, Kiwi in Brazil