New Updated Userform : 15 Column & More Faster Search Method

Excel Advanced Userform & More Faster Data Searching Method

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

           Ago , the searched value is filtered on main sheet, the filtered values are copied to a hidden sheet (FilteredData Sheet), then the data on this hidden sheet are filled into the listbox :
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).count <= 1 Then
GoTo here:
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
End If
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.count, 1).End(xlUp).Row).Value
ActiveSheet.AutoFilterMode = False
Call Clear
          When "Estimated Revenue" is selected as the search column from the ComboBox1, the hidden ComboBox2 is displayed. This combobox contains the operators "=", "<", ">". The value in textbox and with these operators are performed advanced filtering :
Case "Estimated Revenue"
ActiveSheet.AutoFilterMode = False
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

Listbox items can be copied to the other page using ListBox Selection Methods (single ,select,multiple select).

excel userform


  1. Hi,
    Great database - However!!!!

    How come if you paste data into the spreadsheet, it will then not recognise it and as such can not search / nor does showup in the listbox...

    When you make a manual new entry, it just overwrites whatever you inserted into the spreadsheet...

    Also, the Delete button dosnt work

    Im trying to see why all of this is happening, especially the bit where it dosnt recognise the pasted data....

    Any ideas?

  2. Awesome application ... thank you

  3. Excellent...nice updates to already great Userform.

  4. I have Managed to run the VBA onmy Database, However as I am expanding the amount of columns that the(listbox_click) takes into account above 23, it gives me a runtime error, I am a bit stuck here as I have no idea how to fix this and it quite frankly does not make sense to me why it would give me an error on 24 columns and not on 23 columns:

    Private Sub ListBox1_Click()
    Dim say As Long, a As Byte

    For a = 0 To 26
    Controls("textbox" & a + 1) = ListBox1.Column(a)

    say = ActiveCell.Row
    Sheets("Data2").Range("A" & say & ":Z" & say).Select
    TextBox28 = ListBox1.ListIndex + 2
    End Sub

    1. - Loop is based on 26 columns (For a = 0 To 26).
      - Are the text box names correct (TextBox1,TextBox2,TextBox3)?

  5. hai this is a great userform for a newbie like me. i tried to implement your coding in my workbook but however i keep on getting permission denied error on SAVE and CHANGE button. it highlights this code ListBox1.List = Sheets("DataBase").Range("C2:AC" & Sheets("DataBase").Cells(Rows.Count, 1).End(xlUp).Row).Value

    plus, for CHANGE button data in my worksheets is not parallel to my data field.. it shifted to the left where my id number is missing from column "C" and data from cell "D" move to column "C". any idea why??

  6. The Macros have been disabled for this VBA application. plz enable Macros

  7. Hi What is the exact code to select the row on the sheet that is selected on the listbox, I dont want to auto populate textboxes only select the row but i cant seem to find what part of the code that is.

    1. Ok ,
      the following codes only can be used to select the row on the sheet that is selected on the listbox :

      Dim lastrow,say As Long
      lastrow = Sheets("Data").Cells(Rows.count, "A").End(xlUp).Row
      Sheets("Data").Range("A2:A" & lastrow).Find(What:=ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate

      say = ActiveCell.Row
      TextBox15.Value = say
      Sheets("Data").Range("A" & say & ":O" & say).Select

  8. How did u made the list to be 15 columns
    is it a new class?

  9. Nevermind found it :)
    This Excel is beutiful
    thank u

  10. Dear Sir

    I'm not able to download your example file. Sir plz. tell me how to download it plzzzzzzzzzzzzzzzzzzzzzzzzzzz

  11. - Click
    - The "Skip Ad" button appears in the upper right corner.You will be redirected to "" when you press this button.
    - On the top of page(,there is an arrow pointing down.Click it and download file.

  12. Can you improve search that can seaech wild guest

  13. Great, can make the fields other name be dropdown list to select?

  14. Dear sir,
    Your Userform is by far the most versatile and beautiful. My database has 36 columns but 12 of them are selected to put on the Listbox1. So far Listbox1 contents are loaded to the Textboxes correctly when clicked but Activecell.row is not refreshed.

    What does this line do:
    Sheets("Data").Range("B2:B" & LastRow).Find(What:=ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate

    What can I do to make it work?

    Please help.

    Matthew Lee (Canada)

    1. Bonjour , as tu trouvé une solution ?

  15. Hi, I used this userform and its working fine. I just want the increasing serial number (adjustable)in textbox1 and date function in textbox2. if possible plz write the code modification that needed.


  16. This comment has been removed by the author.

  17. Congratulations on teaching and helping people

    My quantity is more than 50 columns.

    How do I use this wonderful Form code

    Thank you, thank you very much.

    I do not speak English

  18. How do we create a combo box for this and still populate the same when the data are clicked in the listbox? any ideas, people? :)

  19. Dear Mr.Kadr,

    Many thanks for this excel tutorial! Would you mind telling me how to format a date column in the listbox? When I input and save a birth date in userform, the format is shown as DD/MM/YYYY in listbox. But when I want to change the data, the date column will be automatically changed to YYYY/MM/DD. Do you have a clue how to solve it? Many thanks.

  20. Hi,
    I have added new textbox for numbering the entries. How do I go about making it autofill the next number in the row?


  21. I have adapted your Exceldatei + VBA for my purposes. Now the "Copy to pace" does not work anymore can you help me there?

  22. hello master, finish adapting this (

    now finished 15 columns is much better, all very well with additem

    I can not modify it RowSorce generates errors Save, change, delete, Clear ...

    I hope master that you will soon adapt it to Rowsource the 15 columns (

    ListBox1.ColumnHeads = True
    ListBox1.RowSource = "Table1"

    thank you very much for file 15 columns

    Excuse my English (city peru)


    1. In Column "First naem" word "Brady Tatum" how to search "Tatum"

  23. download link is not working,
    please send file at this email :

  24. Hi I just started using this userform and it works great (nicely designed) I have done a couple of modifications just to make it useful for my worksheet but I need some help with a couple of more adjustments I have a sheet with a table and I would like to save new records on that table and right now it saves the records on the row below the table the sheet name is: Expenses and the table name is ExpensesTable, the other adjustment I would like to do is to initialize the listbox showing the last records instead of the first ones. I will appreciate any help.