Yes/no msgbox when data validation list item is chosen-VBA

Question

Does anyone know how I can use this code to make a yes no box appear when a list item from data validation is clicked in a certain column?

Basically in the list, they will click "Y" and I want a yes no box to appear asking for confirmation they want "Y".

response = MsgBox("Confirm New Batch Is Beginning", vbYesNo)

If response = vbNo Then
    'do nothing
    Exit Sub
End If

https://spreadsheeto.com/vba-message-box/ -- found on Google. Might be more of a 'superuser' question — Glenn Ferrie, Oct 02 '18 at 12:50

score 1 · Answer 1 · answered Oct 02 '18 at 13:07

1

You can use the worksheet_change evnt This example will kick in in Column A, if the cell changes to "Yes" then the msgbox will kick in. This code belongs in the worksheet module. Right click the sheet tab and select "View Code" that is the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 1 Then    'column 1 is column A
        If Target = "Yes" Then
            Response = MsgBox("Really?", vbYesNo)
            Application.EnableEvents = False
            If Response = vbNo Then
                Target = "No"
            ElseIf Response = vbYes Then
                Target = "Yes"
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

answered Oct 02 '18 at 13:07

Davesexcel

6,896
2
27
42

++ Damn you are fast! :D – Siddharth Rout Oct 02 '18 at 13:07
@OP: Please also have a look at [Things you have to take care of](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) when working with `Worksheet_Change` – Siddharth Rout Oct 02 '18 at 13:09
Is there a way to do what I am asking before the cell is changed? I do not want it to change the cell at all if I answer no to the yes/no box. Basically I would click Y, then if I say no to the confirmation, it will cancel applying the Y to the cell at all. – Jordan Oct 02 '18 at 13:36
Selecting y is the same as entering y when using data validation. That is how the worksheet change event would kick in. Change what you want to happen if they selected no in the msgbox. – Davesexcel Oct 02 '18 at 14:23

Yes/no msgbox when data validation list item is chosen-VBA

1 Answers1