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?

"Spencer" <(E-Mail Removed)> wrote in message
news:ED4630F7-80AC-4CBA-B00D-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "Spencer" <(E-Mail Removed)> wrote in message
> news:ED4630F7-80AC-4CBA-B00D-(E-Mail Removed)...
>>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