CODEWITHSUNDEEP
vbaexcel
Jordan
Jordan

Reputation: 363

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

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

Upvotes: 1

Views: 907

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

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

enter image description here

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

Upvotes: 1

Related Questions