Yes/No answer from pop-up

[pleasehelpthischick]

I have a spreadsheet with a ton of data that I need to filter based on questions the user needs to answer. I already have a pop-up working that asks a yes or no question. If the answer is yes, it filters the right data and we are good. However, if the answer is no, I need another question to pop-up which asks them to select high, medium, or low. Columns H, I, J are high, medium, low respectively. Based on each selection, I will need those columns filtered.

Example:
User hits a button and it asks "is this important?"
User hits yes, everything filters fine.
User hits no, then should be asked "is this high, medium, low?"
If the user selects high, column H will filter to only show the value of High that is in the cell. (The rows only contain either blank or high)
If the user selects low, column I will filter to only show the value of Low that is in the cell. (The rows only contain either blank or low)
The same for the medium option.

Only one selection can be made, high medium or low.

This is the code I have so far that is working:

Sub User_Question()
Dim answer As Integer

answer = MsgBox("Is this system important?", vbQuestion + vbYesNo + vbDefaultButton2, "")

If answer = vbYes Then
ActiveSheet.ListObjects("Table22").Range.AutoFilter Field:=1, Criteria1:= _
"Yes"
Else
ActiveSheet.ListObjects("Table22").Range.AutoFilter Field:=1, Criteria1:= _
"No"

End If
End Sub

 

[Herakles]

This should incorporate everything that you want to do.

Always state the worksheet that you are working on.

Public Sub subPromptToFilter()
Dim strMsg As String
Dim varAnswer As Variant
Dim blnAnswerValid As Boolean
Dim arrCriteria() As Variant
Dim answer As Integer

    On Error Resume Next
    Worksheets("Prompt").ShowAllData
    On Error GoTo 0
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

    If MsgBox("Is this system important?", vbQuestion + vbYesNo + vbDefaultButton2, "") = vbYes Then
    
        Worksheets("Prompt").ListObjects("Table22").Range.AutoFilter Field:=1, Criteria1:="Yes"
        
        MsgBox "Yes"
        
        Exit Sub
        
    End If
    
    strMsg = "1 : High" & vbCrLf & _
                      "2 : Medium" & vbCrLf & _
                      "3: Low" & vbCrLf & _
                      "4 : Show All Rows"
    
    arrCriteria = Array("High", "Medium", "Low")
    
    blnAnswerValid = False
    
    Do While Not blnAnswerValid
    
        varAnswer = InputBox(strMsg, "Enter 1, 2 or 3")
               
        If StrPtr(varAnswer) = 0 Then
            
            MsgBox "User cancelled.", vbInformation, "Warning!"
            
            ' User cancelled. Code here.
            
            blnAnswerValid = True
        
        ElseIf varAnswer = vbNullString Then
            
            ' User did not enter anything. Ask again.
        
        Else
        
            If InStr(1, "1,2,3,4", Trim(varAnswer), vbTextCompare) > 0 Then
                
                On Error Resume Next
                Worksheets("Prompt").ShowAllData
                On Error GoTo 0
                
                ActiveWindow.ScrollRow = 1
                
                ActiveWindow.ScrollColumn = 1
                
                If Val(varAnswer) < 4 Then
                    Worksheets("Prompt").ListObjects("Table22").Range.AutoFilter Field:=Val(varAnswer) + 7, Criteria1:=arrCriteria(Val(varAnswer) - 1)
                End If
                
            Else
        
                blnAnswerValid = False
            
            End If
        
        End If
    
    Loop

End Sub

 

[pleasehelpthischick]

When I run it and click yes I get a debug error on this line:

Worksheets("Prompt").ListObjects("Table22").Range.AutoFilter Field:=1, Criteria1:="Yes"

If I select no, the box comes up to choose high, med, or low but it also says show all rows, which i dont want. If I select 1,2, or 3, I get a debug error on this line:

Worksheets("Prompt").ListObjects("Table22").Range.AutoFilter Field:=val(varAnswer) + 7, Criteria1:=arrCriteria(val(varAnswer) - 1)

Thank you!

 

[Herakles]

You need to state the worksheet that you are on.

Where it says Worksheets("Prompt") you need to change it to make reference to the sheet that you are on.

It is not good practice to use ActiveSheet.

This is for you to build upon, not necessarily the finished code.

Public Sub subPromptToFilter()
Dim strMsg As String
Dim varAnswer As Variant
Dim blnAnswerValid As Boolean
Dim arrCriteria() As Variant
Dim answer As Integer

    On Error Resume Next
    Worksheets("Prompt").ShowAllData
    On Error GoTo 0
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

    If MsgBox("Is this system important?", vbQuestion + vbYesNo + vbDefaultButton2, "") = vbYes Then
    
        Worksheets("Prompt").ListObjects("Table22").Range.AutoFilter Field:=1, Criteria1:="Yes"
                
        Exit Sub
        
    End If
    
    strMsg = "1 : High" & vbCrLf & _
                      "2 : Medium" & vbCrLf & _
                      "3: Low"
    
    arrCriteria = Array("High", "Medium", "Low")
    
    blnAnswerValid = False
    
    Do While Not blnAnswerValid
    
        varAnswer = InputBox(strMsg, "Enter 1, 2 or 3")
               
        If StrPtr(varAnswer) = 0 Then
            
            MsgBox "User cancelled.", vbInformation, "Warning!"
            
            ' User cancelled. Code here.
            
            blnAnswerValid = True
        
        ElseIf varAnswer = vbNullString Then
            
            ' User did not enter anything. Ask again.
        
        Else
        
            If InStr(1, "1,2,3", Trim(varAnswer), vbTextCompare) > 0 Then
                
                On Error Resume Next
                Worksheets("Prompt").ShowAllData
                On Error GoTo 0
                
                ActiveWindow.ScrollRow = 1
                
                ActiveWindow.ScrollColumn = 1
        
                Worksheets("Prompt").ListObjects("Table22").Range.AutoFilter Field:=Val(varAnswer) + 7, Criteria1:=arrCriteria(Val(varAnswer) - 1)
                
            Else
        
                blnAnswerValid = False
            
            End If
        
        End If
    
    Loop

End Sub

 

[pleasehelpthischick]

OK, I made the updates you suggested but when I select high, med, or low, the filter applies but it's not showing the rows, they appear to be hidden. Also, checking the filter, nothing is checked. And the selection box keeps coming up after making a choice.

 

[Herakles]

Have you already got a filter applied before you run the code?

If you cancel what do you see row wise?

Are you using H,I and J for the filter columns?

Add in this line :

blnAnswerValid = True

beneath this line:

Worksheets("Prompt").ListObjects("Table22").Range.AutoFilter Field:=Val(varAnswer) + 7, Criteria1:=arrCriteria(Val(varAnswer) - 1)

 

[pleasehelpthischick]

The box goes away now after answering, so that is fixed. But it's still not filtering properly.
If I select 1 for High, this is what it does. It's put a filter on column I (should be column H) but then nothing is actually filtered but rows are hidden?

 

[pleasehelpthischick]

Hello, nevermind on this. I got it working! I had a typo in the column name for medium, I made those changes and everything runs perfect. Thank you so much for your help!