Autofilter on a form

[ukdane]

Hi,
Is it possible to use a combobox on a form, as an autofilter for a worksheet.
I'm using Excel 2000.

For example on the worksheet "myData" I have a series of columns populated with data.
Column 1= User name
Column 2= Status
Column 3= Invoice
Column 4= Paid

On the form I want 3 comboboxes that give the end user the option to filter the data on the worksheet "myData" in columns 1, 2, and 3. The 3 comboboxes refer to the first 3 columns on the worksheet

The thing is I need the comboboxes to not only list all the options in each column, but also to have an option to show all the data (no filter), or all the blanks in that column.

I hope this makes sense.
Thanks in advance for any help.

[Bob Phillips]

When you load the comboboxes, just add extra items for [All] and [Blanks] and test the code for those before setting the autofilter.

[ukdane]

Thanks, but 1 how do I populate the combobox to resemble the autofilter, and how do I inititate the autofilter, once an item has been chosen?

[Bob Phillips]

Use an array to gather the data, omitting duplicates, then load the combo with the arrays. The selected value is just set as the autofilter criteria.

maybe post your workbook, else we are talking at tangents.

[ukdane]

Use an array to gather the data, omitting duplicates, then load the combo with the arrays. The selected value is just set as the autofilter criteria

Can you give me an example of how I'd use and array to populate a combo, and then how I return the selected value to set the autofilter please.

I can't publish the arc here, as it contains sensative data.

Thanks again.

[ukdane]

I've sorted out how to populate the comboboxes based on arrays, and how to add the "all","blanks","nonblanks".

BUT how do I remove duplicates from the array?

(Excel 2000)


Sub filtercombo()
Dim thisshipment As String
Dim usedshipment As String
Dim max, totalshipments
Dim myselection As String

' place the users selection in the combobox
myselection = ActiveCell.Row 'find the current row
thisshipment = ThisWorkbook.ActiveSheet.Range("C" & myselection).Value
filter_shipment.Value = thisshipment

'populate combobox
On Error Resume Next
max = 1
filter_shipment.AddItem ("All")
filter_shipment.AddItem ("Blanks")
filter_shipment.AddItem ("Nonblanks")
usedshipment = "$C$3:" & ActiveSheet.Range("C65536").End(xlUp).Address
totalshipments = Range(usedshipment).Count 'counts the shipments in the column
Do While max <= totalshipments
filter_shipment.AddItem (Range("C" & 2 + max).Value) 'Put the shipment name in the combobox
max = max + 1 'next shipment!
Loop
End Sub

[ukdane]

Another problem with the code above, is that it includes all the "shipment" names, and it shouldn't do this.
If there is a filter active, then it should only count and list the "filtered" items.

I've changed the code and resolved this now:
Below is an example of what I've changed.
'set shipmentnr
thisshipment = ThisWorkbook.ActiveSheet.Range("C" & myselection).Value
filter_shipment.Value = thisshipment
On Error Resume Next
usedshipment = "$C$3:" & ActiveSheet.Range("C65536").End(xlUp).Address
totalshipments = Range(usedshipment).Count 'counts the shipments on the transport
With filter_shipment
.Clear
For Each cell In Range(usedshipment).SpecialCells(xlCellTypeVisible)
.AddItem cell.Value
Next cell
End With

[ukdane]

I'm still having 2 problems with the code above.

1) The comboboxes, which I'm using for the autofilter, still contain duplicate items. How do I get rid of them?

2) If I select an item which only appears once in the combobox, and it's the first item on the list, then when the autofilter is activated, and I update one of the other comboboxes, instead of taking the data from column "C", it goes amok, and tries to populate the combobox with the contents of the rest of the worksheet, which seems very odd.

Any ideas, and help would be most welcome.