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]

Here is a simple block of code that shows you how you can do that:

    Dim resp
    resp = MsgBox("Do you want to continue?", vbYesNo)
    If resp = vbYes Then
        'continue with code here
    Else
        Exit Sub
    End If

 

[duteberta]

Where exactly would I insert the code? After the Dim declarations?

 

[Joe4]

Where exactly would I insert the code? After the Dim declarations?

Wherever/whenever you want it to pop-up! That call is yours.
If you want it to be the first thing that happens, then right after the variable declarations make sense.

Though you may want to check to make sure that you are in the correct range first.
Otherwise, it will fire anytime any change is made in your sheet.

 

[duteberta]

It needs to come up when someone selects "SOLD" from the dropdown.

So maybe after

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

 

[Joe4]

It needs to come up when someone selects "SOLD" from the dropdown.

So maybe after

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

Try it and see how it works out for you, and if everything is behaving like you want.

Note that because you have a lot of stuff going on after that, it may be best to change the code slightly, and check for when they click "No", and exit there. Otherwise, it can just proceed to the rest of the code, i.e.

    Dim resp
    resp = MsgBox("Do you want to continue?", vbYesNo)
    If resp = vbNo Then
        Exit Sub
    End If
    'rest of code

 

[duteberta]

Ya I can't get it to behave right. If someone selects "NO" then it should exit the sub and the dropdown value should not be allowed to change. Currently the cell value stays "SOLD" even when clicking NO

 

[Joe4]

Ya I can't get it to behave right. If someone selects "NO" then it should exit the sub and the dropdown value should not be allowed to change. Currently the cell value stays "SOLD" even when clicking NO

Understand that the code you have runs AFTER the drop-down box update happens, not before it.
So it cannot "stop" the update from happening, because the update already happened by the time this code ran.
You could set the value in that drop-down to some other value if they click "No", if you like. You would just add that line right above the "Exit Sub" line.

 

[Jaafar Tribak]

Add this new sub to your module:

Sub Undo_()
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End Sub

An then incorporate the sub in the Joe code as follows :

    Dim resp As VbMsgBoxResult
    resp = MsgBox("Do you want to continue?", vbYesNo)
    If resp = vbNo Then
        Call Undo_
        Exit Sub
    End If
   
    'rest of code

 

[duteberta]

Sweet- thank you so much!