Best way to manage product database
"Spencer" wrote in message
...
I could use a little help with this project. i have my design complete.
it
is an order management system. way too simple for the need to use access,
plus i want to distribute it on a common software like excel.
I have a list of products. Each product belongs to a category. I want
the
user to be able to pick a category from a combobox on a userform and have
the
forms list box be populated with the productsin that category.
i have played with using filtering and cannot perfect it. i tried using a
named range (for the product list) but cannot figure out how to not
include
the column headings in the listbox. is there a better technique?
HI Spencer
You appear to have succesfully populated your combobox with categories.
Presumabley you achieved this by adding code to the UserForm_Initialize()
event
Now I think you need to stick some code in ComboBox1_Change() event with
the goal of populating the list box
with products correspopnding to your selection.
I dont know your Data but in general terms the combox change event would do
the following actions once the user form had been called
1) Clear the list box for example ListBox1.clear
2) Set a variable = to the Comboxbox selection
3) Programatically locate the product range and array all members which
index against the variable set in 2) above
You can use many tricks to do this range or cell offset comparisons using
if statments to parse out products not corresponding to the catgory criteria
4) Using additem populate the list box. from the array created in 3)
Something like this where the data sheet has column eE polulated with with
categories andcolumn E is populatyed with corresponding products
Private Sub ComboBox1_Change()
ListBox1.Clear
Dim aaa As String
aaa = ComboBox1.Value
Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
Dim task As Range
Dim prod As String
Set task = Selection
For Each cell In task
prod = ""
If cell.Value = aaa Then prod = cell.Offset(0, 1).Value
If prod < "" Then ListBox1.AddItem prod
Next
End Sub
I wish I could be more specific, so I hope this attempt atl east gets you
going in the right direction.
Best
N10
You now need some code in your
|