Quote:
Originally Posted by NoSparks
If the message box is a must, you need the normal means of deleting the cell contents to initiate it.
As ArviLaanemets says, the Change event firing every time something changes will slow down your worksheet
but depending on the worksheet that may or may not be an issue.
Put this code into the sheet module and give it a try.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
For Each aCell In Target.Cells
If aCell.Value = "" Then
If MsgBox("Are you sure you want to " & _
"delete cell contents ?", 36, "DELETE CELLS ?") <> vbYes Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
Exit For
Next aCell
End Sub
|
Hi!
As I stressed, the change event is fired after you move to another cell! So p.e. you selected cell A2 and cleared cell contents. So long as you don't leave the cell, nothing happens. Then you pressed Tab key - the cursor moves to B2. When this cell is empty, your script undoes last change - which was moving to B2
Probably the only way will be saving at end of your scrip the current selected range to global variable, and at start of scrip (in your For . . Next cycle) you chek the range saved after previous change. And then you need 2 undo's whenever this range is empty and you want to undo deletion.