locked
Search Function in ListBox Excel RRS feed

  • Question

  • Dear All,

    I want to add search function to my product selection form.

    I mean there are lots of items in the list.

    So it would be easier if a text box which we can type the word, so the ListBox shows only the items which includes the text in the search box.

    Could you please help me?

    Thanks for your help,

    Private Sub AddItem_Click()
    
    'Turn off Screen Updating so updating of PO cannot be seen until finished.
    Application.ScreenUpdating = True
    
    'Declare relevant variables
    Dim CurrentProduct As String
    Dim ProductID As String
    Dim UnitPrice As Currency
    Dim ProductCategory As String
    Dim Quantity As Integer
    Dim LineItemTotal As Integer
    Dim POrowstart As Integer
    
    'Information regarding which row to start with for PO
    LineItemTotal = Range("LineItemTotal").Value
    POrowstart = 5
    
    'Get current product selection information from the 'ProductSelection' UserForm
    CurrentProduct = ProductList.Value
    Quantity = QuantityBox.Value
    
    'Lookup related product information from the ProductListing range
    ProductID = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 2, False)
    ProductCategory = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 3, False)
    
    'Populate next line item with product selection
    Range("C" & POrowstart + LineItemTotal).Value = ProductID
    Range("D" & POrowstart + LineItemTotal).Value = CurrentProduct
    Range("F" & POrowstart + LineItemTotal).Value = Quantity
    
    ' Reset Userform values to indicate item was added to PO
    QuantityBox.Value = ""
    labelProductName.Caption = "Ürün Tanımı: "
    labelProductID.Caption = "Stok Kodu: "
    labelProductCategory.Caption = "Ürün Kategorisi: "
    
    'Since PO only has 5 line items, needs to end program if 5 have been selected.  See homework for additional assignment for higher items.
    
    If LineItemTotal = 20 Then
    MsgBox "Your Purchase Order is complete."
    Unload Me
    End If
    
    
    End Sub
    
    Private Sub FinishOrder_Click()
    'Exit Macro
    Unload Me
    End Sub
    
    
    Private Sub ProductList_Click()
    'This macro runs when an item in the Product Selection listbox is selected
    
    'Declare relevant variables
    Dim CurrentProduct As String
    Dim ProductID As String
    Dim UnitPrice As Currency
    Dim ProductCategory As String
    
    'Grab current product from ProductList ListBox selection
    CurrentProduct = ProductList.Value
    
    'Change Product Name label to reflect current item.
    labelProductName.Caption = "Ürün Tanımı: " & CurrentProduct
    
    'Lookup Product ID based on Product Description and change label
    ProductID = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 2, False)
    labelProductID.Caption = "Stok Kodu: " & ProductID
    
    'Lookup Product Category based on Product Description and change label
    ProductCategory = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 3, False)
    labelProductCategory.Caption = "Ürün Kategorisi: " & ProductCategory
    
    'Lookup Unit Price based on Product Description and change label
    
    End Sub
    

    Tuesday, January 30, 2018 1:11 PM

All replies