"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

"N10" wrote in message
...

"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


I meant column E and column D

N10