Message Box upon delete command

[rafikarp]

Hi,

I am looking for a macro that will load a message box everytime a user of a spreadsheet wants to delete a cell or selection.

Basically, if you try to delete a cell, before the clear contents command is carried out, a message box pops up and asks if you wish to delete the cell or not.

The message box part I have (below) but I need coding before it that will make the emssage box show upon the delete command.

If MsgBox("Are you sure you wish to delete this selection", vbYesNo) = vbYes Then Selection.ClearContents​

Any help would be much appreciated. Thanks.

 

[MorganO]

If you are trying to capture the 'Delete' keypress before it deletes what is in a cell, put the following in a module in VBA:

Sub TrapDelKey()
Application.OnKey "{DEL}", "MsgBoxProc"
End Sub
 
Sub UnTrapDelKey()
Application.OnKey "{DEL}"
End Sub
 
Sub MsgboxProc()
If MsgBox("Are you sure you wish to delete this selection", vbYesNo) = vbYes Then Selection.ClearContents
End Sub

Run 'TrapDelKey' to turn on the capture of the 'Del' key. Now when you press the 'Del' key your message box will appear.

Run 'UnTrapDelKey' to put the 'Del' key back in normal ops.

Hope this helps.

Owen

 

[rafikarp]

Thanks! Works well and I have added code to make it run automatically upon opening the file.