Event when "delete" key is pressed over a cell or range?

[cgcamal]

Hi to all,

I'm looking a way to detect when delete button is pressed over a cell or range, and when that happens,
store the content of that cell or range before be deleted (before content disappear) in order to use the
content that will be deleted as a trigger to execute another code.

For some reference I'm using the below code to run some code when a value is entered in any cell, but
now I need to run other code if the content of any cell is deleted and I need to know which content
is deleted.


Is there a way to do this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Ad = Target.Address

If IsEmpty(Range(Ad)) Then
    Exit Sub [COLOR=Green]'If a value is deleted do nothing [B]-->Old code[/B][/COLOR]
[COLOR=Green]    'Now [/COLOR][COLOR=Green]I need to run other code if the content of any cell is deleted.[/COLOR] [B][COLOR=Green]-->New code[/COLOR][/B]
End If
.
.
End sub

Many thanks for any advice.

Best regards

 

[Gary McMaster]

Hi Cesar,

Maybe you could use "OnKey" and intercept the delete key to run your own code, Something like this:

Gary

Option Explicit

Public vCellVal As Variant 'Hold deleted cell value

Public Sub My_Delete()

'Reroute delete key through intercept procedure, Run this procedure first
Application.OnKey "{DELETE}", "New_Delete"

End Sub

Public Sub New_Delete()

'Possibly copy selection to clip board & paste elsewhere, write to disk etc
vCellVal = Selection.Value 'For single cell, store current cell value in Global variable

Selection.ClearContents ' Clear the cell

End Sub

Public Sub Reset()

'Restore delete key to normal function

Application.OnKey "{DELETE}", ""

End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

MsgBox "The last deleted value was: " & vCellVal

End Sub

 

[cgcamal]

Hi Gary,

Many thanks for your help, is very closed. It works if I run first the Application.OnKey code, but I was wondering if it is possible to
do it automatically, without the need to run first the first sub routine.

I mean, it is possible to include a code that works automatically inside"Private Sub Workbook_SheetChange()" procedure?

That when I pressed "Delete" key detects it and give the possiblity to run another code?

I try to avoid the need to run Application.OnKey macro manually each time and try to do it automatically if it is possible.

Maybe something like (In pseudo code)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If "Delete button"=True then
   SelectedCell=Selection.value
   Selection.ClearContents    .
   .
   .
   [COLOR=Green]'Some other code[/COLOR]
End if

End Sub

Many thanks for help so far.

Regards

 

[pgc01]

Hi César

This is a simple code that detects if cell A1 is cleared and gets the value the cell had before it was cleared.

You can test it and then adapt it to your case.

To test it simply paste the code in the worksheet module, write values in A1 and then clear A1. Each time you clear A1 you should get a message with the value in the cell before you deleted it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
 
If Target.Address <> "$A$1" Then Exit Sub
 
If Target.Value <> "" Then Exit Sub ' the cell was not cleared
 
' Get the value the cell had before it was cleared
Application.EnableEvents = False
Application.Undo
v = Target.Value
Target.Clear ' clears the cell again
Application.EnableEvents = True
 
' Do something with the value
MsgBox "The value deleted was: " & v
 
End Sub

 

[cgcamal]

Hi pgc01,

Thanks for reply. Nice idea!!!, It works, I hope not speak so fast, but think I could adapt it to my needs.

Really many thanks for your help, both.

Best regards.

 

[pgc01]

I'm glad it helped. Cheers!