macro to set data validation drop down to blank after selecting item

[howard]

I would like a macro linked to the data validation list in Cell A1 on sheet1 that once item from the drop down has been selected the drop down returns blank

Your assistance in this regard is most appreciated

 

[rlv01]

Perhaps this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValidationRange As Range
    
    Set ValidationRange = Me.Range("C2:C77") '<- Cells with dropdown validation. You must edit in your own range of cells here
    
    If Not Application.Intersect(ValidationRange, Target) Is Nothing And Target.Cells.Count = 1 Then
        On Error Resume Next
           Target.Validation.Delete
        On Error GoTo 0
    End If
End Sub

 

[howard]

Thanks for the help

 

[howard]

I tested your code on Sample Data. After Selecting the item for the drop down, I need it to show Blank in A1 until I select from the drop Down Again


See link below to access my sample data


Kindly test & amend

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[kevin9999]

Does this achieve your aim Howard?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        If Target = "" Then Exit Sub
        Target = ""
        Application.EnableEvents = True
        Exit Sub
    End If
End Sub

 

[howard]

Thanks for the help, Kevin. Code works perfectly

 

[kevin9999]

You're welcome Howard, and thanks for the feedback