Display Search results in ListBox with more than 10 Column ( 17 Column ) in excel VBA ||

Click here to download this file

Display Search results in ListBox with more than 10 Column ( 17 Column ) in excel VBA ||

Option Explicit

Private Sub cmdSearch_Click()

'condition for if blank textbox1

    If Me.TextBox1.Value = "" Then

        MsgBox "PLease enter the search value.", vbOKOnly + vbInformation, "Search"

        Exit Sub

    End If

    

    Application.ScreenUpdating = False

    

    Dim sh As Worksheet ' Database sheet

    Dim sht As Worksheet 'SearchData sheet

    

     Set sh = ThisWorkbook.Sheets("Database")

    Set sht = ThisWorkbook.Sheets("SearchData")

    

    Dim ish As Long 'for the last non-blank row number available in Database sheet

    Dim isht As Long 'for the last non-blank row number available in SearachData sheet

  

  

    Dim iColumn As Integer '' To  select column number in Database sheet

    

  

   

  

    ish = ThisWorkbook.Sheets("Database").Range("C" & Application.Rows.Count).End(xlUp).Row

    'condition if blank combobox1

    If Me.ComboBox1.Value = Empty Then

    MsgBox "Please Select Search Criteria"

    Exit Sub

    End If

    'to generate column number when you select combobox value _

    which must be same to same as worksheet value i.e

    iColumn = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("B3:R3"), 0)

   

    

    'condition for Remove filter from Database worksheet

    

    If sh.FilterMode = True Then

    

        sh.AutoFilterMode = False

    

    End If

    'now Apply filter on Database worksheet

    

    If Me.ComboBox1.Value = "PRODUCT CODE" Then

    

        sh.Range("B3:R" & ish).AutoFilter Field:=iColumn, Criteria1:=Me.TextBox1.Value

    'as PRODUCT CODE is numeric value so Criteria1 = Me.TextBox1.Value for exact match

    Else

    'now search for few keywords for others value of combobox1

        sh.Range("B3:R" & ish).AutoFilter Field:=iColumn, Criteria1:="*" & Me.TextBox1.Value & "*"

    'now filtered in Database sheet

    End If

        'now clear SearchData Sheet if anything present in the sheet

        

        sht.Cells.Clear

        'now copy the filter data from Database sheet

        'paste it into SearchData sheet in A1 cell

        sh.AutoFilter.Range.Copy sht.Range("A1")

        

        'to false copycutmode

        Application.CutCopyMode = False

        'now find out the last non-blank row number from SearchData for Re-Populate ListBox

        isht = sht.Range("A" & Application.Rows.Count).End(xlUp).Row

    'now Populate listbox from SearchData Sheet

        Me.ListBox1.ColumnCount = 17

        

        Me.ListBox1.ColumnWidths = "30,60,150,90,120,80,50,50,50,50,50,50,50,50,50,50,50"

  'condition msgbox if data found

    If isht > 1 Then

        

            Me.ListBox1.RowSource = "SearchData!A2:Q" & isht

            

            MsgBox "Records found"

    Else

    

    MsgBox "No record found."

    

    End If

    sh.AutoFilterMode = False

    Application.ScreenUpdating = True

    

End Sub

Private Sub CommandButton1_Click()

' ---------- Show All ---------------

Dim sh As Worksheet

Set sh = Sheets("Database")

Dim iRow As Long

iRow = sh.Range("C" & Rows.Count).End(xlUp).Row

'to populate listbox  from Database sheet

   With Me.ListBox1

       .ColumnCount = 17

       .ColumnHeads = True

       .ColumnWidths = "30,60,150,90,120,80,50,50,50,50,50,50,50,50,50,50,50"

       .RowSource = "Database!B4:R" & iRow

   End With

   'Now run & See

End Sub

Private Sub UserForm_Initialize()

'to load listbox1 when show this userform

     Dim iRow As Long

     iRow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row 'to find out last non blank row number

     

    With Userform1

       ' to add item on comboBox1

        .ComboBox1.AddItem "Customer/ Parties Name" ' before write see from worksheet same to same

        .ComboBox1.AddItem "Invoice No."

        .ComboBox1.AddItem "Description of Goods"

        .ComboBox1.AddItem "Model NO."

        .ComboBox1.AddItem "PRODUCT CODE"

        

        'to clear filter from sheets

        

        ThisWorkbook.Sheets("Database").AutoFilterMode = False

        ThisWorkbook.Sheets("SearchData").AutoFilterMode = False

        'to clear the SearchData Sheet

        

        ThisWorkbook.Sheets("SearchData").Cells.Clear

        

        '-----------------------------------------------

       'Now Populate ListBox1

       

        .ListBox1.ColumnCount = 17

        .ListBox1.ColumnHeads = True

        .ListBox1.ColumnWidths = "30,60,150,90,120,80,50,50,50,50,50,50,50,50,50,50,50"

        'condition

        If iRow > 1 Then 'means if data present in database then

            .ListBox1.RowSource = "Database!B4:R" & iRow

        Else

            .ListBox1.RowSource = "Database!B4:R4"

        End If

    End With

End Sub