Combobox Add Item

[MBlake]

Hi,
An earlier post reply suggested that using AddItem for a ComboBox, I have
been looking at this and could do with a little more advice please.

I need a ComboBox to give a range of options that are dependant upon a
listbox. I then need a second combobox to take the 1st combobox selected
value and give further choices.

I have created the named ranges, these are - (1) UNIT, (2) categories and
(3) Options. So, categories is dependant upon Unit and Options are then
dependant upon categories.

Can someone advise the code to reference the listbox and then populate the
comboboxes please.

Any help or a url would be excellent,
Mickey

[Ron de Bruin]

Hi Mickey

You can use this

0 = the first item in the listbox

Private Sub ListBox1_Click()
If Me.ListBox1.ListIndex = 0 Then
Me.ComboBox1.List = Sheets("sheet1").Range("UNIT").Value
' other code
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"MBlake" <[email protected]> wrote in message news:[email protected]...
> Hi,
> An earlier post reply suggested that using AddItem for a ComboBox, I have been looking at this and could do with a little more
> advice please.
>
> I need a ComboBox to give a range of options that are dependant upon a listbox. I then need a second combobox to take the 1st
> combobox selected value and give further choices.
>
> I have created the named ranges, these are - (1) UNIT, (2) categories and (3) Options. So, categories is dependant upon Unit and
> Options are then dependant upon categories.
>
> Can someone advise the code to reference the listbox and then populate the comboboxes please.
>
> Any help or a url would be excellent,
> Mickey
>

[vamosj]

After looking at this and doing a little work on my own here is what I've found out.


Private Sub UnitComboBox_Change()
If UnitComboBox.ListIndex = 0 Then CategoryBoxName.ListFillRange = "Worksheet!B4: B10"
If UnitComboBox.ListIndex = 1 Then CategoryBoxName.ListFillRange = "Worksheet!C4: C10"
If UnitComboBox.ListIndex = 2 Then CategoryBoxName.ListFillRange = "Worksheet!D4: D10"
If UnitComboBox.ListIndex = 3 Then CategoryBoxName.ListFillRange = "Worksheet!EB4: E10"
End Sub


You Can do the same in the other Category Box code to produce the options. The Worksheet areas were just ranges I was using. You'll have to set up a separate worksheet with a list of all the values to put into the fill range. It should look like this.
.............. A...................B.....................C
1...........Unit...........Category...........Options
2...........Unit1.........Category1.........Options1
3...........Unit2.........Category2.........Options2
4...........Unit3.........Category3.........Options3



Really hope this helps you out...

[MBlake]

Hi Ron,
Many thanks for your kind reply. I have been a little slow in responding as
I am struggling with this. Can you explain what the line 'other code'
refers to please.

Thanks,
Mickey


"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> Hi Mickey
>
> You can use this
>
> 0 = the first item in the listbox
>
> Private Sub ListBox1_Click()
> If Me.ListBox1.ListIndex = 0 Then
> Me.ComboBox1.List = Sheets("sheet1").Range("UNIT").Value
> ' other code
> End If
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "MBlake" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> An earlier post reply suggested that using AddItem for a ComboBox, I have
>> been looking at this and could do with a little more advice please.
>>
>> I need a ComboBox to give a range of options that are dependant upon a
>> listbox. I then need a second combobox to take the 1st combobox selected
>> value and give further choices.
>>
>> I have created the named ranges, these are - (1) UNIT, (2) categories
>> and (3) Options. So, categories is dependant upon Unit and Options are
>> then dependant upon categories.
>>
>> Can someone advise the code to reference the listbox and then populate
>> the comboboxes please.
>>
>> Any help or a url would be excellent,
>> Mickey
>>
>
>

[Ron de Bruin]

Hi Mickey

In the example if you select the first item in the listbox it will fill
the first combo with this line
Me.ComboBox1.List = Sheets("sheet1").Range("UNIT").Value

Maybe you want to fill more comboboxes with a range that's why I add the
' other code
in the example

You can use another If...End If for the second item in the listbox
If Me.ListBox1.ListIndex = 1 Then
'your code
End If


--
Regards Ron de Bruin
http://www.rondebruin.nl



"MBlake" <[email protected]> wrote in message news:[email protected]...
> Hi Ron,
> Many thanks for your kind reply. I have been a little slow in responding as I am struggling with this. Can you explain what the
> line 'other code' refers to please.
>
> Thanks,
> Mickey
>
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> Hi Mickey
>>
>> You can use this
>>
>> 0 = the first item in the listbox
>>
>> Private Sub ListBox1_Click()
>> If Me.ListBox1.ListIndex = 0 Then
>> Me.ComboBox1.List = Sheets("sheet1").Range("UNIT").Value
>> ' other code
>> End If
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "MBlake" <[email protected]> wrote in message news:[email protected]...
>>> Hi,
>>> An earlier post reply suggested that using AddItem for a ComboBox, I have been looking at this and could do with a little more
>>> advice please.
>>>
>>> I need a ComboBox to give a range of options that are dependant upon a listbox. I then need a second combobox to take the 1st
>>> combobox selected value and give further choices.
>>>
>>> I have created the named ranges, these are - (1) UNIT, (2) categories and (3) Options. So, categories is dependant upon Unit
>>> and Options are then dependant upon categories.
>>>
>>> Can someone advise the code to reference the listbox and then populate the comboboxes please.
>>>
>>> Any help or a url would be excellent,
>>> Mickey
>>>
>>
>>
>
>

[MBlake]

Cheers Ron,
I'll get back to the code now and see what I can work out. I really
appreciate your help and am enjoying Excel despite the headaches :-)

Mickey