Events for cell deletion

[Aaron Queenan]

Are any events raised when cells are deleted?

I have just tested in Excel, and I seem to get a Workbook_SheetChange event,
and I expect the Worksheet_SheetChange and Worksheet_Change events work as
well. The problem is that I can't find any information about what the
arguments mean when cells are being deleted.

Thanks,
Aaron Queenan.

 

[Dick Kusleika]

Aaron

Deleted as in Cleared Contents? If so, you can check whether the target
(the cell that was changed) is empty. Like this

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Target) Then
MsgBox "cell was deleted"
End If

End Sub

 

[Aaron Queenan]

I mean when right-clicking on a cell/range/row/column and selecting
"Delete...". That way it shifts the other cells/rows/columns closer to the
top left, but unfortunately it only gives a Change event containing the new
data in the range, which is pretty useless because I want to find out the
cells which were removed in the process. :-( Is there any other event I
can listen to or property I can check to find out what was in the removed
cells?

Thanks,
Aaron.

 

[Dick Kusleika]

Aaron

Unfortunately, no. One option would be to replace the Delete
commandbarbutton with your own macro that copies the cells, then does the
normal delete. You would have to change three toolbars, namely the Cells,
Row and Column commandbars. Post back if that's something you want to try
and need help with it.

 

[Aaron Queenan]

Thanks, Dick.

Unfortunately, we need a way that will work regardless of whether the user
or a program initiates the command.

Is it possible to listen for commands using the Excel 97 SDK, i.e. to query
Excel for the command handler, then replace it with my own?

Thanks,
Aaron Queenan.

 

[Dick Kusleika]

Aaron

I don't know, but I doubt it.