VBA: Is there any way to detect if DELETE key pressed?

Question

Is there any event to detect if DELETE key pressed on a cell in the sheet?

Here is my sample code:

Public Sub Worksheet_Selection_Change(ByVal Target As Range)
  oldValue = Target.Value
End Sub  

Private Sub Worksheet_Change(ByVal Target As Range)
  newValue = Target.Value
  If oldValue <> newValue Then "Do Somthing"
End Sub

Assume A1=10, you select A1 and delete the value. So newValue=NULL and oldValue=10 Then you Instantly type "20" in the cell, So newValue=20 and oldValue should be NULL, but it is still "10". I need to check if Delete key is pressed to update the oldValue

Private Sub Worksheet_Change(ByVal Target As Range)
  newValue = Target.Value
  If "Delete key Pressed" Then oldValue = ""
  If oldValue <> newValue Then "Do Somthing"
End Sub

There's the KEYPRESS event, as explained here: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/keypress-event — Dominique, Feb 29 '20 at 15:16
That is an event which occurs in an userform. I guess the OP talks about the user pressing the delete key in a worksheet but he is is not clear on it at all. But the answer to his question is anyway [yes](https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell). — Storax, Feb 29 '20 at 15:25
Thanks for replies and sorry for typo in my question. so there is no build-in function to capture key-pressed event specially DELETE key? — Afshin Davoudy, Feb 29 '20 at 17:23
Yes, there is also another [function](https://learn.microsoft.com/en-us/office/vba/api/excel.application.onkey) but as written above your post is not clear what your real question is. — Storax, Feb 29 '20 at 17:37
Maybe this post will help. [Keypress Event](https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell) — REXXman, Feb 29 '20 at 17:41
@REXXman: That is the link I alreday provided in my first comment. — Storax, Feb 29 '20 at 17:47
Sorry for the inconvenience. I added an example to clarify the usage. — Afshin Davoudy, Feb 29 '20 at 20:06
My first advice would be to change oldValue in the change event as well. — Storax, Feb 29 '20 at 20:30
For all practical purposes, there is no difference between pressing delete on a cell and editing the cell, removing the contents and pressing enter. You can detect either from `Worksheet_Change` by checking `IsEmpty(Target.Value)`. — GSerg, Feb 29 '20 at 23:40

score 3 · Answer 1 · answered Feb 29 '20 at 23:30

You can use Application.OnKey to assign procedure to keys.

Add this code to sheet module to activate key hook:

    Private Sub Worksheet_Activate()
        Application.OnKey Key:="{DEL}", Procedure:="DoSomething"
    End Sub

Add this code to sheet module to deactivate key hook:

   Private Sub Worksheet_Deactivate()
       Application.OnKey Key:="{DEL}"
   End Sub

Add this code to standard module:

    Public Sub DoSomething()
      MsgBox "Delete button pressed!"
      Selection.Clear
    End Sub

Selection.Clear command I use because OnKey overwrites the standard behavior of a key. If you don't want to delete content of cells then comment it out.

Afshin Davoudy · Accepted Answer · 2020-03-01T09:57:25.843

Thanks to All helps, I've found a really simple solution to my problem and it doesn't need to check if DELETE key is being pressed. Just need to update oldValue with newValue after Checking the condition.

Public Sub Worksheet_Selection_Change(ByVal Target As Range)
  oldValue = Target.Value
End Sub  

Private Sub Worksheet_Change(ByVal Target As Range)
  newValue = Target.value
  If oldValue <> newValue Then "Do Somthing"
  oldValue = newValue ***
End Sub

VBA: Is there any way to detect if DELETE key pressed?

2 Answers2