CODEWITHSUNDEEP
vbaexcelactionmessagebox
Mark H
Mark H

Reputation: 15

Excel VBA: unable to disable DisplayAlert during drag+drop?

I'm trying to capture a specific drag and drop event in VBA, and would like to disable the popup "There's already data here. Do you want to replace it?" during this event.

I have the basic event of a drag+drop from cell [D1] to cell [E1] captured, but for some reason I'm unable to disable the popup. Does anyone know why?

Thanks so much.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target(1, 1), [D1]) Is Nothing Then
        MsgBox "selected " & Target.Address & " - " & Target(1, 1).Value
        Application.DisplayAlerts = False
    End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target(1, 1), [E1]) Is Nothing Then
        MsgBox "changed " & Target.Address & " - " & Target(1, 1).Value
    End If

End Sub

Upvotes: 1

Views: 278

Answers (2)

CLR
CLR

Reputation: 12279

Try this - it works on my 2013 Excel:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target(1, 1), [E1]) Is Nothing Then
        MsgBox "changed " & Target.Address & " - " & Target(1, 1).Value
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target(1, 1), [D1]) Is Nothing Then
        MsgBox "selected " & Target.Address & " - " & Target(1, 1).Value
        Application.AlertBeforeOverwriting = False
    Else
        Application.AlertBeforeOverwriting = True
    End If
End Sub

This uses the SelectionChange event to catch the user selecting D1 and disables the alert using Application.AlertBeforeOverwriting. Any other selection ensures it's enabled. Dragging the value causes another SelectionChange which now re-enables the alert for any other overwriting.

Also, you ought to use events to trap user clicking in D1 and then changing to another sheet or closing this one as the alerts could remain disabled.

Upvotes: 3

Fajka
Fajka

Reputation: 91

Why did you put

Application.DisplayAlerts = False

after the drag and drop code? Move it before it.

Upvotes: 0

Related Questions