Filtering a ListBox

[Dowsey1977]

Hi,

I have a form in Excel that uses a list box to list the contents of a table on the 'AccOpening' sheet. Also on this form is a combox with a list of names.

What I want to be able to do is filter the contents of the list box dependant on what name is selected in the combobox (linked to column A), and then remove the filter if nothing is selected in the combox.

Is this possible?

[Killian]

How do you want to filter the entries in the list box?

[Dowsey1977]

I think so, I just want the list box to display the filtered information

[Killian]

No, I mean what filter(s) do you want to apply to the list?
A way to achieve this is to repopulate the listbox on the combobox_change event but you need to define what determine the contents of the listbox for each case, i.e.Private Sub ComboBox1_Change()

Select Case ComboBox1.List
Case 0
'add all
Case 1
'add range B1:B24
Case 2
'add range C1:C24

'etc...

End Select


End Sub

[Bob Phillips]

I think so, I just want the list box to display the filtered information

I presume that yopu already have the list for the combobox.

I suggest that you use Atofilter on the data, something like this on the combo click


Private Sub ComboBox1_Click()
LoadListbox Me.ComboBox1.Value
End Sub


and then in a standard module



Sub LoadListbox(val)
Dim rng As Range
Dim rng2 As Range
Dim cell As Range
Dim iLastRow As Long
Dim ary

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1").Resize(iLastRow)
rng.AutoFilter Field:=1, Criteria1:=val
Set rng2 = Range("B2").Resize(iLastRow - 1).SpecialCells(xlCellTypeVisible)
rng.AutoFilter
With Userform1.ListBox1
.Clear
For Each cell In rng2
.AddItem cell.Value
Next cell
End With

End Sub

[Dowsey1977]

Ok....I want to filter on name. So column A in the spreadsheet and the first column of the listbox. I want to select a name from the combobox and then only those records show. The headers are on the first row, and the rest of the data is A2:M & xlLastRow.

I think the re-populating idea looks good, but I'm not sure what the cases would be?

I tried the suggestion from xld, but it didn't work, said something about 'Invalid use of Null'. When I clicked debug, it took me to a line on a different macro.

[Killian]

Well I'm still not clear how your data's structured and how the filters are supposed to behave.. an annotated example might help.
In the meantime, here's an attachment that does what I kind of think you mean (???)

[Dowsey1977]

Excellent, this is exactly what I want it to do! However, I have copied the code into my spreadsheet and whenever I open the form it says 'Permission Denied'??

Any ideas?

[gadz]

why can't I download the attachments? all it gives me is attachment.php

[Killian]

HI gadz and welcome to VBAX :hi:

If you use right-click save as, you'll get "attachment.php"
Just left-clicking the file should allow the attachment.php script to execute and allow you to download

[gadz]

thanks! it's working now. :)