how can I trap rows deleted.....

[Needie]

I want to be able to trap before the user presses delete on selected rows. I need to know what rows the delete was pressed on.

 

[dwilson01]

Not sure if this will work, but try using Application.OnKey and look for the Delete key. Trap if the Delete key is pressed. From Excel Help search for keyword ONKEY

 

[Loomah]

I'm bored so I've been messing around as this is new to me and, building on dwilson01's starter for 10.....
;-)
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{DELETE}"
End Sub

Private Sub Workbook_Open()
Application.OnKey "{DELETE}", "thisworkbook.test2"
End Sub

'make allowances for activating different workbooks
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.OnKey "{DELETE}", "thisworkbook.test2"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.OnKey "{DELETE}"
End Sub

Sub test2()
MsgBox "Delete pressed at row " & ActiveCell.Row
End Sub

 

[xlbo]

Question - do you want to trap the ROW deletion or data in the row being deleted

They will need very different trappers

For the latter (being as this is where this thread is going)
Worksheet_Change
If Target.text = "" then
msgbox "Row " & target.row & " Has had data deleted"
else
end if

However, this won't stop the deletion. Not sure why you need to do this - an explanation of why might help the construction of how
HTH
Geoff

 

[SkipVought]

...
For the former you would have to use the Worksheet_SelectionChange event to SET valus(s) of each cell selected and then in the Worksheet_Change event test each former selected cell for its existance. The NON-EXISTANCE of a formerly selected cell would mean that the CELL HAD BEEN DELETED and not just the text property of the cell. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

xlbo,

Remember that Target in the worksheet events, is Range Object that could be a Collection greater than 1. Best to use a form like...

For Each t In Target
   If t.Value.....

Next t

Skip,
metzgsk@voughtaircraft.com

 

[xlbo]

Very true Skip and thanks for the reminder - however, it wasn't meant to be a full help - still don't know if this is the right track - also still don't know how this could be useful (except to create a log of deletions and even then, would need address rather than row)
G

 

[SkipVought]

xlbo,

Here's how it could work. When a Selection Change is made a range array is set for each cell in the range. The array would be public, redimensioned to the range count.

Then on the Change event, the "change event index" 0 for the first cell, 1 for the next, etc, using that index on the range array object, for any array element, if the error is 424 - Object required, then the corresponding cell has been deleted. You have no idea what the value was; ONLY that a cell in the selection/change range was deleted.

Is that helpful or not? I dunno. Skip,
metzgsk@voughtaircraft.com

 

[rmikesmith]

Here's my 2 cents on the first approach, namely trapping the delete event before it happens. This would allow, in addition to extracting the row info, the option of whether to bypass the deletion. Besides trapping the Delete key, you would need to handle the delete items available on several menus. The following procedures could be used:

Sub SetupTrapping()

'New event handler for Edit/Delete on the Worksheet Menu
CommandBars("Worksheet Menu Bar").Controls("&Edit"). _
Controls(10).OnAction = "EditMenuDelete"
'New event handler for Delete on the Cell Popup Menu
CommandBars("Cell").Controls("&Delete...").OnAction = "CellPopUpDelete"
'New event handler for Delete on the Row Popup Menu
CommandBars("Row").Controls("&Delete...").OnAction = "RowPopUpDelete"

End Sub


Sub EditMenuDelete()
  On Error Resume Next
  'Put your code here to inspect selected row and/or abort deletion.
  'MsgBox for demo/test only
  MsgBox "Inside Edit/Delete Trap", vbInformation + vbOKOnly, ""
  'Invoke Delete method
  Selection.Delete
  
End Sub

Sub CellPopUpDelete()
  On Error Resume Next
  MsgBox "Inside Cells/Delete Trap", vbInformation + vbOKOnly, ""
  'Invoke standard Delete cell dialog
  Application.Dialogs(xlDialogEditDelete).Show
End Sub

Sub RowPopUpDelete()
  On Error Resume Next
  MsgBox "Inside Rows/Delete Trap", vbInformation + vbOKOnly, ""
  Selection.Rows.Delete
End Sub


Sub RestoreAll()
  CommandBars("Worksheet Menu Bar").Controls("&Edit"). _
Controls(10).OnAction = ""
  CommandBars("Cell").Controls("&Delete...").OnAction = ""
  CommandBars("Row").Controls("&Delete...").OnAction = ""
End Sub

Mike