ExcelBanter - user friendly filter

Thank you Ron, it worked perfectly. Excuse me for the delay but I
couldn't find my post back on the group...

Now I try to make the user select the sheet he want to filter.

In a module associated with the button filter I got the following code
:

Public sheetrg As Range
Public st As Variant
Public WS As Worksheet

Sub Button1_Click()

On Error Resume Next
Set sheetrg = Application.InputBox( _
"1/ Use mouse to select the sheet you want to filter 2/select all the
cells in this sheet (ctrl + a) 3/click OK", Type:=8)
On Error GoTo 0
If Not sheetrg Is Nothing Then
MsgBox "You selected the sheet named: " & sheetrg.Parent.Name

For Each WS In ThisWorkbook.Worksheets
If WS.CodeName = sheetrg.Parent.Name Then
Set WS = st
End If
Next WS
FilterMenu.Show vbModeless
Else
MsgBox "Action cancelled"
End If

End Sub

'FilterMenu is a userform that allow the user to select the column he
want to use to filter his data (in a combobox1). Then the unique
values of that column are displayed in a listbox1. Clicking on add
button transfers the selected item to a listbox 2 and erase it from
listbox1 (and he can remove it clicking on the remove button) The list
in the listboxes are sorted in alphabetical order (using a fonction
from Jim Rech 10/20/98). Here is all the code of the FilterMenu
userform. The filter part is missing (I mean that I didn't program how
to get the data from the listbox2 and use it with an advanced filter
on the original data).

Private Sub Userform_Initialize()

With Worksheets(st)
ComboBox1.List = Application.Transpose(.Range(.Cells(1, 1), _
.Cells(1, 1).End(xlToRight)).Value)
End With

End Sub

Private Sub ComboBox1_Click()

ListBox2.Clear
ListBox1.Clear

Dim MF As Variant

Sheets(st).Activate
MF = ComboBox1.List(ComboBox1.ListIndex)
Cells.Find(What:=MF, After:=ActiveCell, LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False).Select

Dim Rng As Range
Dim rng2 As Range

Sheets("Sheet2").Columns(1).ClearContents

With Sheets(st)
Set Rng = .Range(.Cells(1, ActiveCell.Column), .Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
End With

If Rng.Cells.Count < 2 Then Exit Sub

Rng.Advancedfilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=True

With Sheets("Sheet2")
Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, _
1).End(xlUp))
End With

Dim theList As Variant
theList = rng2
QuickSort theList, LBound(theList, 1), UBound(theList, 1)
ListBox1.List = theList

End Sub

Private Sub CommandButton1_Click()
'add button
Dim theList As Variant
For i = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) = True Then
ListBox2.AddItem ListBox1.List(i), 0
ListBox1.Selected(i) = False
ListBox1.RemoveItem i
End If
Next i
theList = ListBox2.List
QuickSort theList, LBound(theList), UBound(theList)
ListBox2.Clear
ListBox2.List = theList
End Sub

Private Sub CommandButton2_Click()
'remove button
Dim theList As Variant
For i = ListBox2.ListCount - 1 To 0 Step -1
If ListBox2.Selected(i) = True Then
ListBox1.AddItem ListBox2.List(i), 0
ListBox2.Selected(i) = False
ListBox2.RemoveItem i
End If
Next i
theList = ListBox1.List
QuickSort theList, LBound(theList), UBound(theList)
ListBox1.Clear
ListBox1.List = theList
End Sub

Private Sub CommandButton3_Click()
'cancel
Me.Hide
End Sub

Private Sub CommandButton6_Click()
Help.Show
End Sub

Sub QuickSort(SortArray, L, R)
'
'Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'
Dim i, j, X, Y
i = L
j = R
X = SortArray((L + R) / 2, LBound(SortArray, 2))

While (i <= j)
While (SortArray(i, LBound(SortArray, 2)) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, LBound(SortArray, 2)) And j L)
j = j - 1
Wend
If (i <= j) Then
Y = SortArray(i, LBound(SortArray, 2))
SortArray(i, LBound(SortArray, 2)) = SortArray(j,
LBound(SortArray, 2))
SortArray(j, LBound(SortArray, 2)) = Y
i = i + 1
j = j - 1
End If
Wend
If (L < j) Then Call QuickSort(SortArray, L, j)
If (i < R) Then Call QuickSort(SortArray, i, R)
End Sub

So my problems a

- I would like to use the variable st (worksheet) in the whole program
but it don't work when the module call the userform. I tried several
variation and searched on this newsgroup for hours but I still got a
problem.

- I would like to fix a problem on the Private Sub ComboBox1_Click().
I would like to search only in the headers of the data sheet (st)
(because it search in the entire sheet, and I want to search only in
the first row)

- I would like to know if any of you have idea how to easely perform
an advanced filter using the data from the userform (st = worksheet
with the data, combobox1 = column name, listbox2 = selected item)

Help you be greatly appreciated 'cause that's been some days I am
strugglin with this macro...

Vincent

"Ron de Bruin" wrote in message ...
Hi Vincent

This will work for the data in the activecell column in Sheet1.
The header cell must be in the first row in this example
The listbox on sheet1 is named ListBox1

I use Sheet2 to copy the Unique cells to
Use a empty sheet for this or use a column on sheet1

Sub test()
Dim rng As Range
Dim rng2 As Range

Sheets("Sheet2").Columns(1).ClearContents

With Sheets("Sheet1")
Set rng = .Range(.Cells(1, ActiveCell.Column), .Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
End With

If rng.Cells.Count < 2 Then Exit Sub

rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=True

With Sheets("Sheet2")
Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, _
1).End(xlUp))
End With

Sheets("Sheet1").ListBox1.List = rng2.Value
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Vincent" wrote in message om...
Hi,

I am a kinda of newbie with VBA and I am working on a macro that would
allow the user to easely filter a huge sheet called "Data". The sheet
data has a variable number of rows and columns and have headers.I am
working with excel 2000 and with userforms.

Part of what I would like to do is:
- in combobox1 the user can choose among the headers of the sheet.
- when he chooses one, the unique value of the columns content is
displayed in listbox1

Example :
Data :
A B C D
1 f 33 t
2 f 34 t
1 g 33 t
2 g 35 u

So combobox1 would display : A, B, C, D

And if the user clicks on A,
listbox1 would display : 1,2
If he clicks on C,
listbox1 displays : 33,34,35

Any ideas?

Vincent
Philadelphia