Popup Window with Yes (delete) / No (don't delete) function

[slam]

I have a data validation drop down in B2 and when a user makes a selection, I want a popup window to appear if there is a value in any cell in the range K2:K41 (also a drop down validation list) which says "Would you like to clear the list?" with a Yes and No option.


If Yes is selected, it will delete the values in K2:K41.


If no is selected, the values are not deleted.


If there is no data in K2:K41, the popup window will not appear.


I would be eternally grateful if someone could assist with this

Thank you

 

[onlyadrafter]

Hello,

This code needs to go into the relevant sheet code window, not a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$B$2" Then
        If Range("K42").End(xlUp).Row > 1 Then
            MY_CHOICE = MsgBox("Would you like to clear the list?", vbYesNo, "Clear K2:K41")
            If MY_CHOICE = vbYes Then
                Range("K2:K41").ClearContents
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Is it working as expected?

 

[slam]

Hello,

This code needs to go into the relevant sheet code window, not a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$B$2" Then
        If Range("K42").End(xlUp).Row > 1 Then
            MY_CHOICE = MsgBox("Would you like to clear the list?", vbYesNo, "Clear K2:K41")
            If MY_CHOICE = vbYes Then
                Range("K2:K41").ClearContents
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Is it working as expected?

That is both so simple, and so genius

Works perfectly, thank you so much!