Programmatically populating combobox cell

[virtuesplea@gma]

I have a spreadsheet in which some of the cells are comboboxes. Because Excel forces me to set a source for each combobox, the source for each is set to " ".
In my VBA code, I retrieve information from an outside source. I need to be able to loop through this information and place each piece of information into the appropriate combobox. The problem is that I have not seen any way of setting the values that should appear in a cell's combobox. I know that I can set the combobox's values list to a range of cells. But I don't want to fill in values into extra cells in the spreadsheet.
So, is there anyway to set the values that should appear in a combobox, if that combobox is one of the cells in a spreadsheet (as opposed to a combobox in a userform)?

[Leith Ross]

Hello Virtuesplea@gma,

Change the worksheet name and combobox (Drop Down) name in the code to match your own. To find the name of the combo (Drop Down as the Forms Toolbar names it), right click it and look in the Excel name box (upper left corner of the formula bar).

    With WorkSheets("Sheet1").DropDowns("Drop Down 1")
      .RemoveAllItems
      .AddItem "Apples"
      .AddItem "Oranges"
      .AddItem "Grapes"
      .AddItem "Pears"
      .AddItem "Kiwi"
    End With

Sincerely,
Leith Ross

[mikerickson]

If you want to do it all in one shot, fill an array and then run the line

ActiveSheet.DropDowns("myDropdown").List = myArray

[virtuesplea@gma]

I tried both of the posted solutions and get the following error message:

"Run-time error '1004':
Unable to get the DropDowns property of the Worksheet class"

Looking in the Object Browser, I found that the Worksheet class has a DropDowns method (which returns Object), but not a property of that name.

I also tried:
With Sheet1.DropDowns("D4")
.RemoveAllItems
.AddItem "Apples"
.AddItem "Oranges
End With

With that code, I get the following error:
"Run-time error '1004':
Method 'DropDowns' of object '_Worksheet' failed"

Any other suggestions? BTW, I am using Excel 2003 with VBA 6.3. It's for my job, so I have to use those versions.

Thanks

[Leith Ross]

Hello Virtuesplea@gma,

There are 2 types of ComboBox controls that can be added to a worksheet. The Forms type whose name will appear as "Drop Down "n" where n is a number, and the Control Toolbox whose name is is usually Combox"n" where "n" is a number, unless the user has changed the name. Since you are receiving a Run-Time error I suspect you have Control Toolbox ComboBoxes on the worksheet. Here is the code for that type of ComboBox...

With ComboBox1
  .Clear
  .AddItem "apples"
  .AddItem "oranges"
  .AddItem "pears"
End with

If you still have problems, post your workbook.

Sincerely,
Leith Ross

[mikerickson]

Use "Record a macro", and move the drop down a little. That will show you how the machine refers to the dropdown.