BeforeCellDeletion event in Excel?

[leassaf]

Hello chaps!

I'm trying to detect when a user deletes a range of cells in excel. The ideal would be the presence of an event that would fire just before the range of cells is being deleted. In that way I would know information about the data that is about to disappear.

Apparently there's no such event for a worksheet.

Is there there a way of doing that?

 

[bryanbayfield]

Put this code in the workbook object:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnKey "{DELETE}", "run_check"
Application.OnKey "{BACKSPACE}", "run_check"
End Sub

Then put this code in a standard module:

Sub run_check()
If Not ActiveWindow.RangeSelection Is Nothing Then
MsgBox "Value of first cell deleted was " & ActiveWindow.RangeSelection.Cells(1, 1).Value
ActiveWindow.RangeSelection.Cells.ClearContents
End If
End Sub

This should be what you're after. The code in the workbook object will call the standard module code when either <delete> or <backspace> is pressed in the workbook. I don't know what functionality you want when cells are cleared but it will probably be analogous to what I've used. This code tells you the value of the cell in the top left of the range.

Hope this helps.

Bryan.
: