Home > Excel Tutorial > Excel Advanced Userform : Listbox That Contains 15 Columns

Excel Advanced Userform : Listbox That Contains 15 Columns

More Faster Search Method In Userform

         We have edited the listbox in userform as 15 columns in this tutorial and changed the data search method to get faster results and used “Autofilter Method”. The related codes :
“Select Case ComboBox1.Value
Case “First Name”
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range(“A1:O” & Sheets(“Data”).Cells(Rows.count, 1).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=TextBox13.Value & “*”, Operator:=xlAnd
Sheets(“FilteredData”).Cells.Clear
…”

The searched value is filtered on data sheet, the filtered values are copied to a hidden sheet (FilteredData Sheet), later the data on this hidden sheet are populated into the listbox :

“If ActiveSheet.Range(“A1”).CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).count <= 1 Then
GoTo here:
Else
ActiveSheet.Range(“A2:O” & Sheets(“Data”).Cells(Rows.count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets(“FilteredData”).Range(“A2”)
End If
Sheets(“FilteredData”).Columns.AutoFit
ListBox1.List = Sheets(“FilteredData”).Range(“A2:O” & Sheets(“FilteredData”).Cells(Rows.count, 1).End(xlUp).Row).Value
here:
ActiveSheet.AutoFilterMode = False
Call Clear
…”

When “Estimated Revenue” is selected as the search column from the combobox-1, the hidden combobox-2 is viewed. This combobox contains the operators “=”, “<“, “>”. The value in textbox and with operators are performed advanced filtering :

“Case “Estimated Revenue”
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case “0”
ActiveSheet.Range(“A1:O” & Sheets(“Data”).Cells(Rows.count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:=”=” & TextBox13.Value
Case “1”
ActiveSheet.Range(“A1:O” & Sheets(“Data”).Cells(Rows.count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:=”<” & TextBox13.Value
Case “2”
ActiveSheet.Range(“A1:O” & Sheets(“Data”).Cells(Rows.count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:=”>” & TextBox13.Value
End Select
…”

The listbox items can be copied to the other sheet using listbox selection methods (single select-multiple select).

Download The Sample Workbook

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: