Help to filter list

Hello. About a year ago I watched a vba lesson on youtube to create a inventory management system. I have the system running but wanted to make a few improvements and would appreciate any type of help.

I have attached the file. In the form/system, what I am trying to do is be able to type a word and have the list filter items that matches that word only. So if i type for eg. "apple" only listings I see are items with "apple".

THere are 2 product fields, I am looking to do this under the "sales/purchase transactions" section. Clicking the product dropdown shows all the products. But once this list gets long, it gets a bit harder to find what you are looking for. So trying to see if I can change it so that typing the word will filter out the search.

Thank you in advance.

Try this
change the Combobox (cmb_Product) style from frmStyleDropDownList to frmStyleDropDownCombo

Code:

---

Private Sub cmb_Product_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    Dim Typematch As String
    Dim i      As Long

    Typematch = LCase(cmb_Product.Text)
   
    With Me.cmb_Product
        For i = 0 To .ListCount - 1
            On Error Resume Next
            If LCase(.List(i)) Like "*" & Typematch & "*" Then
            'do nothing
            Else
            .RemoveItem (i)
            End If
            On Error GoTo 0
        Next i
    End With
   
    If Len(cmb_Product.Text) = 0 Then Call Refresh_Product_List
   
End Sub

---

I just thought to give a try. Definitely there will be a better way to do this, which the expert members in this forum may explain

I'd set the control's MatchEntry = fmMatchEntryFirstLetter

Attachment 28780

Quote:

---

Originally Posted by Paul_Hossler

I'd set the control's MatchEntry = fmMatchEntryFirstLetter

Attachment 28780

---

Wouldnt this just match the first letter? Im trying to see if I can match words, multiple letters. Thanks.

Quote:

---

Wouldn't this just match the first letter?

---

Yes, but

1. It's easy
2. It's fast
3. It gets you close

I think I've seen what you're asking about, but IIRC it involves reading keystrokes, repositioning the display, etc. Whole lot of work

Quote:

---

Originally Posted by Paul_Hossler

Yes, but

1. It's easy
2. It's fast
3. It gets you close

I think I've seen what you're asking about, but IIRC it involves reading keystrokes, repositioning the display, etc. Whole lot of work

---

Yeah, it works currently with the first letter. Issue is with a large list, there can be many products that start with the same letter. Trying to find a solution for that.

Not exactly using the Combobox, but uses a Listbox as a search box

Has the advantage of being easy

There's a number of efficiencies that could be made, especially if the list is sorted, but the complexity didn't seem worth it

Thanks for sharing this Paul!

Tweaked the xlsm a little

Thanks. This is what im looking for, but being limited in code, not sure where to put/change this code :(.

Attachment 28783

Replace the Product combobox with a textbox and a list box

Integrate the event handlers from test_2


Test_2 only has the Product selection and the Save button working

Lot of existing code will need to be modified and the Userform format changed for cosmetics

Thanks. This seems a bit to complicated for my skillset. Was trying to see if there really was a simple solution, doesnt look like there is.

It really isn't too complicated

Replace your combobox with a textbox to type in and a list box to pick from

In Userform_Initialize, build an array of the Products (or whatever)

Code:

---

Option Explicit


Dim aryProducts As Variant


Private Sub UserForm_Initialize()
    Dim r As Range




    With Worksheets("Product_Master")
        Set r = .Cells(2, 2)
        Set r = Range(r, r.End(xlDown))
   
        aryProducts = Application.WorksheetFunction.Transpose(r)
    End With
   
    Me.tbSearch.Text = vbNullString


End Sub

---

In Textbox_Change, fill the Listbox (Listbox AddItem) with Products that start with what is in the Textbox

Code:

---

Private Sub tbSearch_Change()
    Dim i As Long
    With Me
   
        .lbProduct.Clear
       
        If Len(.tbSearch.Text) = 0 Then Exit Sub
   
       
        For i = LBound(aryProducts) To UBound(aryProducts)
            If UCase(Left(aryProducts(i), Len(.tbSearch.Text))) = UCase(.tbSearch.Text) Then
                .lbProduct.AddItem aryProducts(i)
            End If
        Next i
   
    End With


End Sub

---

Save/Process/Select the listbox item that is highlighted

Code:

---

Private Sub CommandButton8_Click()
    With Me
        If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value
       
        .Hide
    End With
   
    Unload Me


End Sub

---

Thanks Paul. I think whats really throwing me off isnt just trying to get the search to run the way I want, but also because the search is "linked" to other components in the sheet. Like picking a product initiates adding/removing from the inventory and also adding to the daily sales and profit margins. This is what I feel is throwing me off because adding the search code might mess these other links.

Really new to this so I might be completely off base. I'll try to play around with your tips to see if I can get anywhere.

Thanks a bunch for your assistance!

Quote:

---

Originally Posted by Paul_Hossler

It really isn't too complicated

Replace your combobox with a textbox to type in and a list box to pick from

In Userform_Initialize, build an array of the Products (or whatever)

Code:

---

Option Explicit


Dim aryProducts As Variant


Private Sub UserForm_Initialize()
    Dim r As Range




    With Worksheets("Product_Master")
        Set r = .Cells(2, 2)
        Set r = Range(r, r.End(xlDown))
   
        aryProducts = Application.WorksheetFunction.Transpose(r)
    End With
   
    Me.tbSearch.Text = vbNullString


End Sub

---

In Textbox_Change, fill the Listbox (Listbox AddItem) with Products that start with what is in the Textbox

Code:

---

Private Sub tbSearch_Change()
    Dim i As Long
    With Me
   
        .lbProduct.Clear
       
        If Len(.tbSearch.Text) = 0 Then Exit Sub
   
       
        For i = LBound(aryProducts) To UBound(aryProducts)
            If UCase(Left(aryProducts(i), Len(.tbSearch.Text))) = UCase(.tbSearch.Text) Then
                .lbProduct.AddItem aryProducts(i)
            End If
        Next i
   
    End With


End Sub

---

Save/Process/Select the listbox item that is highlighted

Code:

---

Private Sub CommandButton8_Click()
    With Me
        If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value
       
        .Hide
    End With
   
    Unload Me


End Sub

---

---

Look at this line and insetad of MsgBox use .lbProduct.Value in your other places

Code:

---

If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value

---

Quote:

---

Originally Posted by Paul_Hossler

Look at this line and insetad of MsgBox use .lbProduct.Value in your other places

Code:

---

If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value

---

---

What would I do with cmb_Product? This is what the combobox was and a lot of code is associated with that? Just replace cmb_Product with tbsearch?

Post #13

Yes

Replace cmb_Product and it's event code with tbSearch and lbProduct and the event code in my example.

Then in Commandbutton8_Click use lbProduct.Value for the follow on processing

Sorry, still a bit confused. For eg. have this:

Sub Refresh_Product_List()

Application.ScreenUpdating = False

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product_Master")

Dim i As Integer

Me.cmb_Product.Clear
Me.cmb_Product.AddItem ""
If Application.WorksheetFunction.CountA(sh.Range("A:A")) > 1 Then
For i = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))
Me.cmb_Product.AddItem sh.Range("B" & i).Value
Next i
End If

End Sub


Here, theres calls to cmb_product here. ANd I believe more like this across other code. I understand removing cmbbox and adding listbox along with the new code. But still confused on these older references. Thank you.

Quote:

---

Originally Posted by Paul_Hossler

Post #13

Yes

Replace cmb_Product and it's event code with tbSearch and lbProduct and the event code in my example.

Then in Commandbutton8_Click use lbProduct.Value for the follow on processing

---

Quote:

---

Here, there's calls to cmb_product here. And I believe more like this across other code. I understand removing cmbbox and adding listbox along with the new code. But still confused on these older references. Thank you.

---

If you replace Combobox with a Textbox and a Listbox, then all the uses of the Combobox will have to be changed accordingly

The Test button opens the userform, and the Search Textbox searches product names that match. Clicking a Product Name in the Listbox populates Qty and ID (example of using the selection)

Attachment 28799


FWIW, I like to

1. Define Const values for key columns to make the code easier to interpret

2. Put all non-Userform initialization code in a separate sub

3. Use small subs/functions to improve readability

Code:

---

Option Explicit


Public aryProducts As Variant
Public aryID As Variant
Public rData As Range


Public Const colID = 1
Public Const colProduct = 2
Public Const colPrice = 3
Public Const colAvail = 4
Public Const colStock = 5


'========================================================================================
Sub drv()


    Init


    Load frm_Inventory_Management
    frm_Inventory_Management.Show
End Sub
'========================================================================================




Sub Init()
    Dim r As Range
   
    With Worksheets("Product_Master")
        Set r = .Cells(2, colProduct)
        Set r = Range(r, r.End(xlDown))
   
        aryProducts = Application.WorksheetFunction.Transpose(r)
   
        Set r = .Cells(2, colID)
        Set r = Range(r, r.End(xlDown))
   
        aryID = Application.WorksheetFunction.Transpose(r)
   
        Set rData = .Cells(1, colID).CurrentRegion
    End With


End Sub




Function rowLookupByProduct(s As String) As Range
    Dim r As Long
   
    r = Application.WorksheetFunction.Match(s, rData.Columns(colProduct), 0)
   
    Set rowLookupByProduct = rData.Rows(r)
End Function


Function rowLookupByID(s As String) As Range
    Dim r As Long
   
    r = Application.WorksheetFunction.Match(s, rData.Columns(colID), 0)
   
    Set rowLookupByID = rData.Rows(r)
End Function

---