Warning before overwriting existing data in VBA-CodePudding

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

CodePudding user response：

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