mrkaya
01-16-2018, 03:39 AM
Hi Everyone,
I have been trying to put a search box in my product list for hours but couldn't succeed so far.
This is the "product selection form":
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
I need to put a search box on it. So, when a letter is pressed, it filters only these products.
Secondly, I'm trying to use mouse wheels in the list but it doesn't work.
Any help would be appreciated.
Thanks!
I have been trying to put a search box in my product list for hours but couldn't succeed so far.
This is the "product selection form":
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
I need to put a search box on it. So, when a letter is pressed, it filters only these products.
Secondly, I'm trying to use mouse wheels in the list but it doesn't work.
Any help would be appreciated.
Thanks!