listbox / multi selection filter

[ahmad123]

Hi folks,

I have a userform containing a listbox. In this listbox, theres data imported from a column ("Ext Co Visibility Ds") in a big spreadsheet.
What i need to do is to give the user the capability to filter the big spreadsheet by selecting multiple lines in the listbox.

The listbox has to act like a filter for the column "Ext Co Visibility Ds". By filtering that column the user will see the rows corresponding to his selection in the big spreadsheet.

below is my code :

Dim rngVendors As Range
Private Sub lbx_Vendors_Click()
 
 
 
    If Me.lbx_Vendors.ListIndex = -1 Then Exit Sub
    Application.ScreenUpdating = False
    On Error Resume Next
    rngVendors.AutoFilter Field:=rngVendors.Column, Criteria1:=Me.lbx_Vendors.Text
 
    Application.ScreenUpdating = True
 
End Sub
Private Sub UserForm_Initialize()
 
    Const VendorCol As String = "I"
    Const HeaderRow As String = "1"
    Sheets("sheet1").Select
    Dim rngUniques As Range
    Set rngUniques = Cells(HeaderRow, Columns.Count).End(xlToLeft).Offset(0, 6)
    Set rngVendors = Sheets("sheet1").Range(VendorCol & HeaderRow, Cells(Rows.Count, VendorCol).End(xlUp))
 
    Application.ScreenUpdating = False
    rngVendors.AdvancedFilter Action:=xlFilterCopy, _
                              CopyToRange:=rngUniques, _
                              Unique:=True
    Set rngUniques = Range(rngUniques.Offset(1, 0), Cells(Rows.Count, rngUniques.Column).End(xlUp))
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=rngUniques, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet1").Sort
        .SetRange rngUniques
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    If rngUniques.EntireColumn.Hidden = False Then rngUniques.EntireColumn.Hidden = True
    Application.ScreenUpdating = True
 
    Me.lbx_Vendors.RowSource = rngUniques.Address
 
End Sub

And can u please modify the code so i will be able to make multi selections in the listbox ???




Thanks to the Gurus <TABLE id=post2543652 class=tborder border=0 cellSpacing=0 cellPadding=6 width="100%" align=center><TBODY><TR><TD class=thead>

</TD></TR><TR><TD style="PADDING-BOTTOM: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 0px" class=alt2><!-- user info --><TABLE border=0 cellSpacing=6 cellPadding=0 width="100%"><TBODY><TR><TD noWrap> <!-- BEGIN TEMPLATE: postbit_onlinestatus -->

</TD><TD width="100%"></TD><TD vAlign=top noWrap>



</TD></TR></TBODY></TABLE><!-- / user info --></TD></TR><TR><TD id=td_post_2543652 class=alt1><!-- message, attachments, sig --><!-- icon and title -->
<HR style="BACKGROUND-COLOR: #ebebeb; COLOR: #ebebeb" SIZE=1><!-- / icon and title --><!-- message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_start --><!-- END TEMPLATE: ad_showthread_firstpost_start --><!-- / message --><!-- attachments -->

</TD></TR></TBODY></TABLE>

 

[ahmad123]

Any ideas !!!

 

[depcdivr]

set the multiselect property of the listbox to "fmMultiselectMulti"