List box query

[kryt0n]

Morning all!

Hopefully a quick question.

I know how to create listboxes in forms and then insert them into a worksheet.

However how do you create a list box that then filters a worksheet, not populate it. Please see my attached .xls

[romperstomper]

Not much to go on there - what is supposed to be in the listbox to do the filtering? Column A data?

[kryt0n]

Sorry about that, yes I would like the list box in form to have the data from column A. When the user then presses 'Submit' after selecting an item from the listbox in userform it filters the worksheet behind

[romperstomper]

Code for the form (slightly different from normal as you have a List on the worksheet):

Please Login or Register  to view this content.

[kryt0n]

Thank you,

That does work great however the listbox contains all the data in column A, ie, not a compacted list of just the names but they are repeated.

Anyway of just displaying the names without having them repeat themselves?

[romperstomper]

Yes - change the Initialize sub to this:

Please Login or Register  to view this content.

[kryt0n]

'Fraid now it doesnt filter the spreadsheet when a name's selected

please see attached .xls

(new one)

[kryt0n]

Sorry - it does work. Excel was having a moment.

Thank you!

[kryt0n]

Can I ask another question?

Say I wanted to filter 2 or more columns, How could I do that so you would only hit one submit button?

please see attched .xls

[romperstomper]

Since you have two lists on your worksheet for some reason, you would filter one then the other.

[kryt0n]

Currently the code looks like this but can't upload it to show you as the file is too big.

Const mcstrKEY_COLUMN As String = "A"
Private Sub btnSubmit_Click()
If Me.ListBox1.ListIndex > -1 Then
Sheet1.ListObjects(1).Range.AutoFilter field:=1, Criteria1:=Me.ListBox1.Value
End If
End Sub

Private Sub UserForm_Initialize()
Dim lngLastRow As Long, n As Long
Dim objDic As Object
Dim varList
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
' use a Dictionary object to hold items so we can create unique list
Set objDic = CreateObject("Scripting.Dictionary")
varList = Range(Cells(2, mcstrKEY_COLUMN), Cells(lngLastRow, mcstrKEY_COLUMN)).Value
For n = LBound(varList) To UBound(varList)
' check if item is already in dictionary
' if not, add it
If Not objDic.exists(varList(n, 1)) Then
objDic.Add varList(n, 1), CStr(n)
End If
Next n
' assign dictionary items to listbox
Me.ListBox1.List = objDic.keys
Set objDic = Nothing
End Sub

[romperstomper]

I know - I wrote it. The answer is still the same.

[kryt0n]

Ok, tried playing around and I'm lost.

How do I code it to filter one, then the other?

(Also, how would I code a button to clear all filters?)

[romperstomper]

Ahh, the ever-expanding question...

Roughly:

Please Login or Register  to view this content.

to reset:

Please Login or Register  to view this content.

[kryt0n]

I'm so close and yet so lost!

I'm trying to edit it but not quite having any luck...

Const mcstrKEY_COLUMN As String = "A"
Private Sub btnSubmit_Click()
If Me.ListBox1.ListIndex > -1 Then
Sheet1.ListObjects(1).Range.AutoFilter field:=1, Criteria1:=Me.ListBox1.Value
End If
If Me.ListBox2.ListIndex > -1 Then
Sheet1.ListObjects(2).Range.AutoFilter field:=1, Criteria1:=Me.ListBox2.Value
End If
End Sub

Private Sub CommandButton1_Click()
Sheet1.ShowAllData
End Sub

Private Sub UserForm_Initialize()
Dim lngLastRow As Long, n As Long
Dim objDic As Object
Dim varList
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
' use a Dictionary object to hold items so we can create unique list
Set objDic = CreateObject("Scripting.Dictionary")
varList = Range(Cells(2, mcstrKEY_COLUMN), Cells(lngLastRow, mcstrKEY_COLUMN)).Value
For n = LBound(varList) To UBound(varList)
' check if item is already in dictionary
' if not, add it
If Not objDic.exists(varList(n, 1)) Then
objDic.Add varList(n, 1), CStr(n)
End If
Next n
' assign dictionary items to listbox
Me.ListBox1.List = objDic.keys
Set objDic = Nothing
End Sub

[romperstomper]

I am many things, but psychic is not one of them. What is the specific problem you are having? (besides the fact you don't seem to be putting anything into your second listbox)

[kryt0n]

Honestly because I'm struggling to follow the bit of code you wrote for updating the first listbox

I tried copying it and editing it as I thought best but keep getting errors. I was too embarrased to post it on here

[romperstomper]

I guess:

Please Login or Register  to view this content.

[kryt0n]

Thank you

Looks like I really was 90% there with my edit. Ok, now I can do it!

You're a star for helping me today.

[romperstomper]

No problem. Please don't forget the marking as 'Solved' thing.

[kryt0n]

Hey again

This might not be possible but in the userform list box is there anyway of it displaying (all), (Blanks), (Top 10) etc as it does in the worksheet list option?

[romperstomper]

Yes, it could be done but Top 10 only applies to numbers. Either way, it's a different question.