advance userform and filter

[luvbite38]

Hi guys,

I am hoping to get some help from you guys,

I am using a userform with 5 combo boxes to filter a table and it works like a charm....it filters the table and copies the filtered results into a new table.

I need a tweak in that code, if possible.....

is that possible to return the filtered results (which are returned in a sheet called SearchedResults) in the listbox1 which I just entered in same userform....In addition I want another listbox2 which should sit parallel to the listbox1 and user can select one project from the listbox1 and when double click or click the button that particular select results would go into the listbox2... From there I will run a code to open the selected search result from listbox2 in another userform ( I have that part sorted already).

the form should look something like this:


search criteria 1 ________
search criteria 2 ________
search criteria 3 ________
search criteria 4 ________
search criteria 5 ________

Button name (search)


List Box1 ListBox2
1 aa 1 aa
2 sd
3 a >>
4 sa
5 fr

Button Name (Edit)

Hope it makes sense?

Please please help me out with this query

Thanks in advance.

 

[luvbite38]

by the way I am using the following code to filter from userform

Private Sub CommandButton1_Click() ' Run Autofilter based on text boxes
Application.ScreenUpdating = False
Sheets(SR).Activate
Columns("A:AP").Select

Selection.EntireColumn.Hidden = False
Range("A1048576").End(xlUp).Select
Selection.CurrentRegion.ClearContents

Dim aSheet As Object
Sheets("ProposalData").Activate
With Rows("1:1")
If ComboBox1 <> "" Then .AutoFilter Field:=1, Criteria1:=ComboBox1
If ComboBox2 <> "" Then .AutoFilter Field:=2, Criteria1:=ComboBox2
If ComboBox3 <> "" Then .AutoFilter Field:=5, Criteria1:=ComboBox3
If ComboBox4 <> "" Then .AutoFilter Field:=13, Criteria1:=ComboBox4
If ComboBox5 <> "" Then .AutoFilter Field:=19, Criteria1:=ComboBox5
End With
If ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 = 0 Then MsgBox "No data, meeting the criteria, was found.", vbCritical, "Search Results": Exit Sub Else




' CheckResults ' display message and set Button enable

Sheets("ProposalData").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(SR).Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets(SR).Activate
Range("AL:AO,W:AJ,N:Q,K:L,F:I,C:C").Select
Range("C1").Activate
Selection.EntireColumn.Hidden = True

'''''''' code to test





''''''''''''''''''''''''''''''''''''''''


Unload Me
MsgBox "The searched results are tabulated, please click the proposal no. you may wish to edit", vbInformation, "Searched Results Information"
UserForm1.Show

FitPage
Application.CutCopyMode = False
Application.ScreenUpdating = True

 

[luvbite38]

the list boxes beneath the combo boxes should look something like that

and it should only allow to move one entry from list box 1 to listbox2

http://vbacentral.blogspot.com.au/2009/11/excel-hidingunhiding-sheets-using-user.html