2

I am currently trying to build an Excel UserForm, where part of it is used to search the worksheet "data".

The part I'm struggling with is the search procedure. There's a listbox that's used to show all the data relative to the combobox (this is an array. For example Vehicle, Stationary, Food & Gifts, Clothing, Training and Business Expenses).

I have this working fine, I have a textbox I want to use to refine (reference data in the column B) the search in conjunction with the combobox (category) , but don't know how to program it.

The code I have so far is as follows:

Private Sub cboSearch_Change() 
    'this is for the header row, 6 columns 
    Listbox1.Clear
    Listbox1.AddItem

    For x = 0 To 6
        Listbox1.List(0, x) = Worksheets("Data").Cells(1, x + 1)
    Next x

    Listbox1.Selected(0) = True

    ' this is for the data. 6 columns
    For I = 1 to Worksheets("Data").Range("A645536").End(xlUp).Row
        If Worksheets("Data").Cells(I, "A") = Me.cboSearch Then
            Listbox1.AddItem 

            For x = 0 To 6
                 Listbox1.List(Listbox1 ListCount - 1, x) = Worksheets("Data").Cells(I, x + 1)
            Next x
        End if
    Next i
End Sub

I don't know how to incorporate the textbox to narrow the search down on column B in Listbox1 which is an Outlet (or shop)

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Cam
  • 21
  • 2
  • 1
    Can't you use another `IF` statement within your `If Worksheets("Data").Cells(I, "A") = Me.cboSearch Then` to check if `Worksheets("Data").Cells(I, "B") = Me.TextBox1`? – JvdV Aug 02 '19 at 07:33
  • 2
    Here is how I would do it instead of looping though all that range. **1.** Use Autofilter to filter Col A based on `cboSearch` and filter Col B based on `Textbox` **2.** Once I have the filtered range, I will store that in the listbox. You may want to see [Multicolumn Listbox add non contiguous range](https://stackoverflow.com/questions/10621758/excel-vba-multicolumn-listbox-add-non-contiguous-range) **Note:** Working with autofilter and arrays will make your application very fast :) – Siddharth Rout Aug 02 '19 at 07:44
  • I'm very sorry, but I don't know how or where to code it in. – Cam Aug 02 '19 at 09:34
  • 1
    Can you provide a screenshot of your sheet? I would go with @SiddharthRout suggestion. Much better option in terms of process design and performance – Zac Aug 02 '19 at 10:41

0 Answers0