Warning before overwriting existing data in VBA

Question

I have the below code that creates a pop-up when new data is pasted into the specified range (A15 : E33). What I would like is when the user attempts to paste data into the range the pop up shows up and if the user selects no the data isn't pasted, preventing accidental overwrite.

Currently when the user selects no all it does is prevent cell B2 from being timestamped.

Thank you in advance for your help

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$15:$E$33" Then

Dim answer As Integer
answer = MsgBox("You are about to overwrite existing data, would you like to continue?", vbQuestion + vbYesNo)

  If answer = vbYes Then
     Range("B2") = "=NOW()"
        Range("B2").Copy
        Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("a15:e33").Select
    Else
    MsgBox "Cancelled"
    End If
  
  End If


End Sub

FaneDuru · Accepted Answer · 2022-11-15 14:39:41Z

2

Your code cannot know about your intention to paste in a specific range...

The above code is an event, being automatically triggered when the pasting has already been done. What you can do is using Application.UnDo:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.address = "$A$15:$E$33" Then

 Dim answer As VbMsgBoxResult
 answer = MsgBox("You are about to overwrite existing data, would you like to continue?", vbQuestion + vbYesNo)

  If answer = vbYes Then
       Application.EnableEvents = False 'to avoid the event being triggered again...
           Range("B2") = "=NOW()"
            Range("B2").Copy
            Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Range("a15:e33").Select
        Application.EnableEvents = True
 Else
            Application.EnableEvents = False 'to avoid the event being triggered again...
                Application.Undo
            Application.EnableEvents = True
            MsgBox "Cancelled"
        End If
  End If
End Sub

edited Nov 15, 2022 at 14:39

answered Nov 15, 2022 at 14:33

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FaneDuru Over a year ago

@RBRN1231 Didn't you find some time to test the above code event? If if tested, didn't it behave as you need?

Collectives™ on Stack Overflow

Warning before overwriting existing data in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related