Excel Userform Listbox That Contains 15 Columns
The benefits of using the userform are indisputable in order to enter data into the worksheet and analyze the data on worksheet more easily and to make changes on the data.
➨For this purpose, we designed a userform with advanced features. Unlike other userforms -as well as record adding, deleting and editing features-, we have added features such as listbox multiple selection feature, advanced search feature, textbox that shows the index sequence number . The item or items selected on the listbox can be copied to the other worksheet.
✓ 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 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.
✓ 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
Sheets("FilteredData").Cells.Clear
✓ The listbox items can be copied to the other sheet using userform listbox selection methods (single select,multiple select).
Private Sub OptionButton1_Click() ListBox1.MultiSelect = 0 End Sub Private Sub OptionButton2_Click() ListBox1.ListIndex = -1 ListBox1.MultiSelect = 1 End Sub Private Sub OptionButton3_Click() ListBox1.ListIndex = -1 ListBox1.MultiSelect = 2 End Sub
