VBA vbYesNo Inquiry

Question

I am trying to create a message box that will give the user the option to continue or stop if their search comes up with more than 1000 results. I have the message box made, but I don't know how to code the vbYes and the vbNo to either continue on with the code (vbYes) or to end the script (vbNO).

Here is my code.

Sub FindOne()

Range("B19:J5000") = ""

Application.ScreenUpdating = False

Dim k As Integer, EndPasteLoopa As Integer, searchColumn As Integer, searchAllCount As Integer
Dim myText As String
Dim totalValues As Long
Dim nextCell As Range
Dim searchAllCheck As Boolean

k = ThisWorkbook.Worksheets.Count
myText = ComboBox1.Value
Set nextCell = Range("B20")
If myText = "" Then
    MsgBox "No Address Found"
    Exit Sub
End If

Select Case ComboBox2.Value
    Case "SEARCH ALL"
        searchAllCheck = True
    Case "EQUIPMENT NUMBER"
        searchColumn = 1
    Case "EQUIPMENT DESCRIPTION"
        searchColumn = 3
    Case "DUPONT NUMBER"
        searchColumn = 6
    Case "SAP NUMBER"
        searchColumn = 7
    Case "SSI NUMBER"
        searchColumn = 8
    Case "PART DESCRIPTION"
        searchColumn = 9
    Case ""
        MsgBox "Please select a value for what you are searching by."
End Select

For I = 2 To k
    totalValues = Sheets(I).Cells(Rows.Count, "A").End(xlUp).Row
    ReDim AddressArray(totalValues) As String

    If searchAllCheck Then
        searchAllCount = 5
        searchColumn = 1
    Else
        searchAllCount = 0
    End If

    For qwerty = 0 To searchAllCount
        If searchAllCount Then
            Select Case qwerty
                Case "1"
                    searchColumn = 3
                Case "2"
                    searchColumn = 6
                Case "3"
                    searchColumn = 7
                Case "4"
                    searchColumn = 8
                Case "5"
                    searchColumn = 9
            End Select
        End If

        For j = 0 To totalValues
            AddressArray(j) = Sheets(I).Cells(j + 1, searchColumn).Value
        Next j

        If totalValues > 1000 Then
            Results = MsgBox("Your Search has Returned Over 1000 Results. Continuing Could Cause Excel to Slow Down or Crash. Do you Wish to Continue?", vbYesNo + vbExclamation, "Warning")
        End If

        If Results = vbNo Then
            End
        End If

        If Results = vbYes Then
            For j = 0 To totalValues
                If InStr(1, AddressArray(j), myText) > 0 Then
                    EndPasteLoop = 1
                    If (Sheets(I).Cells(j + 2, searchColumn).Value = "") Then EndPasteLoop = Sheets(I).Cells(j + 1, searchColumn).End(xlDown).Row - j - 1
                    For r = 1 To EndPasteLoop
                        Range(nextCell, nextCell.Offset(0, 8)).Value = Sheets(I).Range("A" & j + r, "I" & j + r).Value
                        Set nextCell = nextCell.Offset(1, 0)
                     Next r
                End If
            Next j
        Else
            End
        End If
    Next qwerty
Next I
Debug.Print tc
Application.ScreenUpdating = True
End Sub

@Nathan_Sav When I added `If results vbYes then`, It doesn't search through the script in vbYes and and only gives some results if I click on the "No" button. If I click on the yes button, it just continues popping up the pop up. I updated my code above to show the `If Results...` statement. — Caleb Sutton, Jun 29 '17 at 14:33
@Nathan_Sav Sorry, that's what I meant to type. I already had that as shown above in the code. I still am getting the same issues. — Caleb Sutton, Jun 29 '17 at 14:38
`totalValues ` still accrues after so it shows because of you not exiting the loop on yes — Nathan_Sav, Jun 29 '17 at 14:40

score 2 · Answer 1 · answered Jun 29 '17 at 14:22

2

If I understood your problem, you have to compare the "Results" variable if is vbYes or vbNo. Below a little and simple example.

If MsgBox("Continue?", vbYesNo,"Confirmation") = vbYes Then  
 'code if yes
Else     
 'End
End If

Hope that helps. ;)

answered Jun 29 '17 at 14:22

Rafael Ribeiro

188
7

When I added `If results vbYes then`, It doesn't search through the script in vbYes and and only gives some results if I click on the "No" button. If I click on the yes button, it just continues popping up the pop up. I updated my code above to show the `If Results...` statement. – Caleb Sutton Jun 29 '17 at 14:33
Where did you declare the "Results" variable? – Rafael Ribeiro Jun 29 '17 at 14:44
At `Results = MsgBox("Your Search has Returned Over 1000 Results. Continuing Could Cause Excel to Slow Down or Crash. Do you Wish to Continue?", vbYesNo + vbExclamation, "Warning")` Right after `If TotalValues > 1000 Then` – Caleb Sutton Jun 29 '17 at 15:14

score 1 · Answer 2 · answered Jun 29 '17 at 14:25

1

vbYes is a constant, a member of an enum called VbMsgBoxResult that defines a bunch of related constants, including vbYes and vbNo.

If vbYes Then

That's like saying

If 42 Then

You have a constant expression that evaluates to a Long integer, and an If statement works with a Boolean expression that evaluates to a Boolean value (True/False).

You need to compare vbYes to something to get that Boolean expression.

If Results = vbYes Then

answered Jun 29 '17 at 14:25

Mathieu Guindon

69,817
8
107
235

When I added `If results vbYes then`, It doesn't search through the script in vbYes and and only gives some results if I click on the "No" button. If I click on the yes button, it just continues popping up the pop up. I updated my code above to show the `If Results...` statement. – Caleb Sutton Jun 29 '17 at 14:33
I've no idea what you're talking about, "it" doesn't "search through the script", it runs the code you're telling it to run. Of course it continues popping up the pop up, you're in a loop, so you get the prompt at 1001 results, 1002 results, 1003 results, ...210923 results... what did you expect? Your logic needs to be massively streamlined, but that's beyond the scope of this site. You had one specific problem, this is one specific answer. – Mathieu Guindon Jun 29 '17 at 14:39

VBA vbYesNo Inquiry

2 Answers2