ComboBox or ListBox

[K_Man95]

I utilize a ComboBox to allow the user to choose a value from ONE column on Sheet2. The value they choose then gets used in a "FILTER" process to only show the rows with the value chosen in ComboBox1. If the row does NOT contain the same value in ComboBox1, the entire row is hidden. Basically, I have a Worksheets with LOADS of data, by using a ComboBox and the FILTER code, I am allowing the user to VIEW ONLY the data they want to see based on what value they have chosen in ComboBox1. Here comes the NEXT PHASE question....

I would like to allow the user to have multiple criteria choices in order to filter the sheet from. Right now, ComboBox1 has a ListFillRange from one column. Once the user chooses a value as the filter criteria, the FILTER CommandButton is clicked and any row that doesn't have that value in it gets hidden. Here is the FILTER code I have used:

Private Sub CommandButton1_Click()
Dim myVal As String, i As Long, lastRow As Long

myVal = ComboBox1.Value

With Sheets("Sheet3")
.Cells.EntireRow.Hidden = False
lastRow = .Range("A65536").End(xlDown).Row

For i = 1 To lastRow
If .Cells(i, "D") = myVal Then
.Rows(i).Hidden = False
Else
.Rows(i).Hidden = True
End If
Next i
End With
Sheets("Sheet3").Select
End Sub

First problem, If I utilize MULTIPLE ComboBoxes, how can I edit the above code to search for the values from BOTH ComboBoxes? Do I simply make the following change to the above code:

myVal = ComboBox1.Value And ComboBox2.Value

Next question, would it be easier to use a ListBox? If so, how?

Yes, I am sure that I may have to explain this a bit better, but for now, I am not finding the right wording. Simply stated, I want to give the user more choices to choose from in order to focus the data they want to view. There are many rows that have the same value in the one column I am using as the ListFillRange for ComboBox1. In order to provide a more robust tool, I need to provide another column of data and have the above Filter code filter the worksheet of the value from both ComboBoxes.

Hope this make sense. If not, I am here for questions. Thanks in advance for the help.

 

[Norie]

I think it would be easier to use a listbox.

You could set it's MultiSelect property to true.

You would then loop through each item in the list and hide/show the rows based on whether they have been selected or not.

Perhaps something like this, untested, code.

Private Sub CommandButton1_Click()
Dim myVal As String, i As Long, lastRow As Long

With Sheets("Sheet3")
    .Cells.EntireRow.Hidden = False
    lastRow = .Range("A65536").End(xlUp).Row

    For i = 1 To lastRow
        For j = 0 To ListBox1.ListIndex - 1
        
            If ListBox1.Selected(i) Then
                myvale = ListBox1.List(i)
                .Rows(i).Hidden = .Cells(i, "D") = myVal
            End If
        Next j
    Next i

End With

End Sub

 

[K_Man95]

Didn't seem to work for me.....

I think it would be easier to use a listbox.

You could set it's MultiSelect property to true.

You would then loop through each item in the list and hide/show the rows based on whether they have been selected or not.

Perhaps something like this, untested, code.

Private Sub CommandButton1_Click()
Dim myVal As String, i As Long, lastRow As Long

With Sheets("Sheet3")
    .Cells.EntireRow.Hidden = False
    lastRow = .Range("A65536").End(xlUp).Row

    For i = 1 To lastRow
        For j = 0 To ListBox1.ListIndex - 1
        
            If ListBox1.Selected(i) Then
                myvale = ListBox1.List(i)
                .Rows(i).Hidden = .Cells(i, "D") = myVal
            End If
        Next j
    Next i

End With

End Sub

I get an error message on the highlighted line above. The error message reads as follows:

ListBox1.Select(i) = <Could not get the Selected property. Invalid argument.

Any ideas?

Also, the (xlUp) statement does NOT seem to do much until I change it to (xlDown). That is when I get the error message mentioned above. When I use the (xlUp), NOTHING happens. No rows are hidden... I don't get redirected to Sheet 3.... absolutely NOTHING happens.

Any ideas?

 

[Norie]

ListBox1.Select(i) =

That line isn't in my code.

There are a few typos though.

  For j = 0 To ListBox1.ListCount - 1
    
        If ListBox1.Selected(i) Then
            myvale = ListBox1.List(j)
            .Rows(i).Hidden = .Cells(i, "D") = myVal
        End If
    Next j

What is the actual error message?

When do you get the error message.

Like I said it's untested and was really only meant for illustration.

By the way using xlDown with A65536 you will always return the value 65536 for the row number.

 

[K_Man95]

ListBox1.Select(i) =

That line isn't in my code.

There are a few typos though.

  For j = 0 To ListBox1.ListCount - 1
    
        If ListBox1.Selected(i) Then
            myvale = ListBox1.List(j)
            .Rows(i).Hidden = .Cells(i, "D") = myVal
        End If
    Next j

What is the actual error message?

When do you get the error message.

Like I said it's untested and was really only meant for illustration.

By the way using xlDown with A65536 you will always return the value 65536 for the row number.

Here is the code that I have installed behind the CommandButton:

Private Sub CommandButton2_Click()
Dim myVal As String, i As Long, lastRow As Long

With Sheets("Sheet3")
.Cells.EntireRow.Hidden = False
lastRow = .Range("A65536").End(xlDown).Row

For i = 1 To lastRow
For j = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(i) Then
myvale = ListBox1.List(j)
.Rows(i).Hidden = .Cells(i, "D") = myVal
End If
Next j

Next i

End With

End Sub

Here is the ERROR MESSAGE I am getting:

Run-time error '-2147024809 (80070057)':

Could not get the Selected property. Invalid argument.

This error message happens once the code gets to the following line:

If ListBox1.Selected(i) Then

That is the line that is highlighted when I go to debug the error message. Does this help?

 

[Norie]

Did you try changing the i to j as I did in the amended code?