Consulting

Results 1 to 19 of 19

Thread: Help to filter list

  1. #1

    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.
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Try this
    change the Combobox (cmb_Product) style from frmStyleDropDownList to frmStyleDropDownCombo

    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
    Attached Files Attached Files

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I'd set the control's MatchEntry = fmMatchEntryFirstLetter

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Quote Originally Posted by Paul_Hossler View Post
    I'd set the control's MatchEntry = fmMatchEntryFirstLetter

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

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Quote Originally Posted by Paul_Hossler View Post
    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.

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks for sharing this Paul!

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Tweaked the xlsm a little
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Capture.JPG

    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    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.

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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)


    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

    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

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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    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 View Post
    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)


    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

    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

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

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Look at this line and insetad of MsgBox use .lbProduct.Value in your other places

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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    Quote Originally Posted by Paul_Hossler View Post
    Look at this line and insetad of MsgBox use .lbProduct.Value in your other places

    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?

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #18
    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 View Post
    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

  19. #19
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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)

    Capture.JPG


    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


    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •