excelvbamsgboxdata-security

Warning before overwriting existing data in VBA


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

Solution

  • 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