Trap delete key

Question

My problem is fairly trivial: I need to apply logic to the delete button in Excel. In a related question I asked for means to clear cells in a pivot table, and realising now that this might not be the right approach, this is yet another alternative I'm considering. Unfortunately, I admittedly have little experience with Visual Basic, and this task is proving such a challenge (quite surprisingly).

Is it possible to trap (as in listen to) the delete-key in Excel and trigger a subroutine in VBA when pressed?

I'd appreciate all kind of input! Thanks!

Answer 1

Yes.

You case use Application.Onkey to assign code to keys

If you (1) add this code to a sheet module that contains your pivottable (right-click your sheet tab, View Code and past the code below)

This code activates and then de-activates the intercept when the user enters then leaves this particular sheet:

Private Sub Worksheet_Activate()
Application.OnKey "{DELETE}", "Intercept"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{DELETE}"
End Sub

(2) add this code to a regular module

Sub Intercept()
MsgBox "user just pressed delete"
End Sub

then delete on that sheet will be trapped and the user given a message