Dropdown selection requires YES/NO authorization before script continues

[duteberta]

Not sure how to get a message box inside this code.

I would like to have a NO/YES message box if the user selects "SOLD" from the dropdown. A "YES" selection would allow the script to proceed, if "NO" then the "SOLD" selection would not be allowed and the script would stop.

How (where) would I insert the message box into the following code?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim rngCol1 As Range
    Dim rng2 As Range
    Dim LO As ListObject
    Dim lColCnt As Long
    Dim i As Long
    Dim v As Variant

    Set LO = Me.ListObjects(1)
    lColCnt = LO.ListColumns.Count

    Set rngCol1 = Intersect(Target, LO.Range.Columns(3))

    If Not rngCol1 Is Nothing Then
        With Application
            .EnableEvents = False
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With

        ReDim v(1 To lColCnt)

        For Each rng In rngCol1
            If LCase(rng.Value) = LCase("SOLD") Then
                i = 0

                'Remember the numerical formats of each column
                For Each rng2 In LO.ListRows(rng.Row - LO.Range.Row).Range
                    i = i + 1
                    v(i) = rng2.NumberFormat
                Next rng2

                '"paste" values
                rng.Resize(, lColCnt).Value = rng.Resize(, lColCnt).Value

                'Restore original formats of each column
                For i = 1 To lColCnt
                    LO.ListRows(rng.Row - LO.Range.Row).Range.Cells(1).Offset(, i - 1).NumberFormat = v(i)
                Next i
            End If
        Next rng

SortTable

        With Application
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With
    End If

End Sub

 

[Joe4]

Nice amendment Jaafar!
Thanks for chiming in!