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
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
..."
            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:
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 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
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
..."

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

excel advanced userform

20 comments:

  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?

    ReplyDelete
  2. Excellent...nice updates to already great Userform.

    ReplyDelete
  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)
    Next

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

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

      Delete
  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??

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

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

    ReplyDelete
    Replies
    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").Activate
      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

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

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

    ReplyDelete
  9. Dear Sir

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

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

    ReplyDelete
  11. Can you improve search that can seaech wild guest

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

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

    Thanks,
    Matthew Lee (Canada)

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

    Subhash
    (India)

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  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

    ReplyDelete