Populating multiple comboboxes in VBA

[quick62silver]

I am new to using the combobox. All of the comboboxes are in the same column and each have the same four items to select. I am not using a Userform. I want to use VBA to populate the multiple comboboxes. I do not want to specify each combobox individually as follows

ComboBox1.AddItem "Item 1"
ComboBox1.AddItem "Item 2"
ComboBox1.AddItem "Item 3"
ComboBox1.AddItem "Item 4"
ComboBox2.AddItem "Item 1"
ComboBox2.AddItem "Item 2"
ComboBox2.AddItem "Item 3"
ComboBox2.AddItem "Item 4"
...
ComboBoxN.AddItem "Item 1"
ComboBoxN.AddItem "Item 2"
ComboBoxN.AddItem "Item 3"
ComboBoxN.AddItem "Item 4"

Is there a way to populate all of the comboboxes in a loop instead of specifying them like above?

Please advise.

 

[SkipVought]

Hi,

What is the purpose? This is not how multiple comboboxes are usually employed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I'd GUESS that you want to populate multiple rows in a column from a combobox?

You could use Data > Validation -- LIST in that range, if that's the case.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[quick62silver]

Thanks Skip for the quick reply. Sorry that this is not explained completely but here are more details. Hopefully this helps.

The column will have a combobox (ActiveX control) on each row for let's say 100 rows. They would contain the same 4 items for each combobox. The user is to select which of the 4 items they want to for that row and subsequently enter information on that row that further identifies the row. I wanted to populate each one of these comboboxes through VBA when the Worksheet becomes Active. I can populate these comboboxes individually fine by specifying each of the comboboxes AddItem property. So if I had 100 rows I would have 400 "AddItem" properties, 4 for each combobox. Obviously, this is cumbersome.

I am simply looking for a way, if possible, to maybe use a looping mechanism in VBA to "walk through" each of the 100 comboboxes to populate each one with the 4 items.

If this is not possible (or I am using this in the wrong manner) then what would be the best approach to do this? I am building this in Excel 2007 but it needs to work in Excel 2003 as well.

Thanks in advance

 

[SkipVought]

As I stated in my surmise, you can use the Data > Validation -- LIST feature, without any VBA!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Agreed that data validation could be the way to go but as I don;t get to code vba much at the mo I thought I would answer the specific question anyway:

Dim ctl As OLEObject

Dim cb As ComboBox

For Each ctl In ActiveSheet.OLEObjects

    If ctl.OLEType = 2 Then

        Set cb = ctl.Object

        cb.Clear

        cb.AddItem 1
        cb.AddItem 2
        cb.AddItem 3
        cb.AddItem 4
   
    End If

Next ctl

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

If I were going to use a combobox control, I would insert ONE control on the sheet and position, size & make it visible, in the Worksheet_SelectionChange event.

Then asign the combobox result to the row.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

agreed -

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question