Tuesday, November 22, 2016

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 advanced 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. Excellent...nice updates to already great Userform.

  3. 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)?

  4. 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??

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

  6. 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

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

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

  9. Dear Sir

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

  10. - Click http://adf.ly/1fydVg
    - The "Skip Ad" button appears in the upper right corner.You will be redirected to "google.com/drive" when you press this button.
    - On the top of page(google.com/drive),there is an arrow pointing down.Click it and download file.

  11. Can you improve search that can seaech wild guest

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

  13. 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)

  14. 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.


  15. This comment has been removed by the author.

  16. 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

  17. 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? :)

  18. 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.

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