VBA Code to run on selection of drop down menu

**lealea1982** · 02-23-2012, 06:27 AM

Hi all,

Here's what I have:
A worksheet where, in most cells of column A, I have a data validation drop down menu where the options are "delete" or (blank). There is various data in three columns to the right of that.

Here's what I need:
A VBA code that will, on selection of "delete" from a drop down menu, come up with a box that says "Are you sure you wish to delete this row?" with the options 'yes' and 'no', and then delete that entire row if the user clicks 'yes', and if they click 'no' all it does is change the selection back to (blank).

This is maybe a really easy one but I just can't quite get it right!

Thanks for your help!

**Bob Phillips** · 02-23-2012, 06:40 AM


Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Me.Range("E2:E200")) Is Nothing Then
    
        If Target.Value = "delete" Then
        
            If MsgBox("Are you sure that you want to delete this row", vbYesNo, "Delete Row") = vbYes Then
            
                Target.EntireRow.Delete
            Else
            
                Target.ClearContents
            End If
        End If
    End If

ws_exit:
    Application.EnableEvents = True
End Sub