Add Items On A dynamic created combobox

[vtops]

Dear All,

I am Constructing a macro command on Excel.
My Macro Must Create some comboboxes and then fill them with some data from a sheet.

The first step is completed (creating comboboxes)

The Problem Is how to fill them with data.
Because they are creating dynamically and i don't know on design mode how many will be created and their names.
If i use in design mode the property additem like this: Combobox1.Additems "testing"
It refuses to compile it, because the combobox1 doesn't exists.
So I Would like some help.


Thank you very much everybody

 

[SkipVought]

Hi,

When you add your control, assigne the OBJECT in a Set statement to an Object Variable. Use the AddItem method on the Object Variable.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[CautionMP]

Relative control references. If the [tt]ComboBox[/tt] is on a form:

Dim ctlToUpdate As Control
Set ctlToUpdate = Controls("[b]Combobox1[/b]")
With ctlToUpdate
  .AddItem "test1"
End With

This will allow you to write all the code you need for these controls whithout getting a compile errors during design.

Note: You won't get the helper flyouts in the VBE pane when using this method.

Hope this helps,
CMP


Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[rmikesmith]

If you set an object variable reference to the dynamically created combobox then you can use that with the AddItem method. Here is an example (where CB is declared MSForms.ComboBox):

Set CB = Me.Controls.Add("Forms.ComboBox.1", "Combo1", True)
For Each OneCell In Worksheets(1).Range("Items")
  .AddItem OneCell.Text
Next OneCell

You could use this type of construct in a loop to create/populate a number of comboboxes (determined at run-time). To interact with these controls later, use the name given in the creation step (If using a loop, make the name something like "Combo" &i, where i is the loop counter variable).

Hope this helps,
Mike

 

[vtops]

Ok Thaks everybody about your interesting i will try it immediately.


Best Regards