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 are:
- 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
