msgbox when deleting cells

Mikedk64 · #1 07-12-2017, 12:18 AM

Hi,

Is there a way to display a msgbox, when someone is trying to delete cells in a worksheet?
For example, if I am trying to delete (A32, B32, C32, D32, E32, F32, G32, H32) I want a msgbox saying "Are you sure that you want to delete cells" And then a yes or no choise.

ArviLaanemets · #2 07-12-2017, 06:07 AM

Hi

Practically the only event you can use is OnChange - which is fired AFTER you move cursor from the cell you did change - not very easy at least. When you can somehow to get information, what was the last cursor location before current one, and is this cell empty now, then maybe you can take a step back, when you check No - but not very likely.

And remember - the OnChange event is fired whenever you make any change on any worksheet! So your workbook will be slow down considerabely.

NoSparks · #3 07-12-2017, 07:04 AM

@ Mikedk64
Are you actually deleting the cells or clearing the contents ?
ie: the delete key clears the contents whereas right clicking gives option to clear contents or delete which then asks about shifting cells.

Logit · #4 07-12-2017, 07:29 AM

Code:

Option Explicit

Sub Test3()
Dim answer As Variant
  answer = MsgBox("Are you certain these cells are to be deleted ?", vbYesNo + vbQuestion, "Delete Cells ?")
  If answer = vbYes Then
      Call Test1
   Else
     Exit Sub
End If
End Sub

Sub Test1()
   'Code here to delete or clear cells
   MsgBox "Running macro"
End Sub

Mikedk64 · #5 07-12-2017, 08:12 AM

*Logit:

Where should I insert this code, and can i just copy paste it, or do I need to modify it

Quote:

Originally Posted by Logit

Code:

Option Explicit

Sub Test3()
Dim answer As Variant
  answer = MsgBox("Are you certain these cells are to be deleted ?", vbYesNo + vbQuestion, "Delete Cells ?")
  If answer = vbYes Then
      Call Test1
   Else
     Exit Sub
End If
End Sub

Sub Test1()
   'Code here to delete or clear cells
   MsgBox "Running macro"
End Sub

Mikedk64 · #6 07-12-2017, 08:14 AM

* NoSparks:

Sorry for not being clear

I mean when I am pressing the delete key.

Quote:

Originally Posted by NoSparks

@ Mikedk64
Are you actually deleting the cells or clearing the contents ?
ie: the delete key clears the contents whereas right clicking gives option to clear contents or delete which then asks about shifting cells.

Logit · #7 07-12-2017, 08:25 AM

.
Paste the macro code into a Routine Module. You would activate it by way of a command button located on the worksheet that will have the cells deleted or cleared.

If you can be more specific about which cell / s you intend to delete or clear (and are you deleting the cell altogether or are you only clearing the contents from the cell) ?

NoSparks · #8 07-12-2017, 04:29 PM

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

ArviLaanemets · #9 07-12-2017, 10:54 PM

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.

NoSparks · #10 07-12-2017, 11:35 PM

The file I tested with.

ArviLaanemets · #11 07-13-2017, 12:01 AM

Quote:

Originally Posted by NoSparks

The file I tested with.

I myself never use any events in Excel VBA except Open event. I assumed, that Change event behaves like the built-in one which contros autocalculation - after you leave the cell. When not, then it is similar to contlrol's Change event in Access - it is fired after every keystroke or mouse operation. I.e. when I type 10-character word into some cell, the Change event is fired 10 times! OK. An additional cause never use it

NoSparks · #12 07-13-2017, 07:14 AM

Hopefully the OP will try the suggestion.

Don't know if they'll return or not.

A week ago the OP got a solution suggested 13 minutes after posting this question at another forum and didn't acknowledge it for 4 days, apparently not getting it to work. Suspect they inserted a regular module and put the code there rather than in the worksheet module.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trigger Beep or MsgBox when key combo accidentally pressed	NotQuiteANovice	Word VBA	1	04-07-2017 05:41 PM
Deleting rows based on the text in certain cells VBA	ThisGuyJohn	Excel Programming	5	02-03-2017 03:16 PM
On Open MsgBox for use in Template sent out to colleagues to gather information	Lancashire Lad	Word VBA	2	10-26-2015 10:22 AM
Deleting info from specific table cells	jamierbooth	Word VBA	3	07-29-2014 03:20 PM
Make MsgBox appear...	Jamtart	PowerPoint	3	09-01-2012 08:21 AM