Combo-box and vba

[vidar78]

Hi,
I have a set of 50 comboboxes in an excel sheet, and I am trying to write values to them through a for-statement... but I can't figure out how.
I have done it by using a form in access, with this code:

For count = 1 To 50
Me("ComboBox" & count) = array(count)
Next

But of course, I can't get the Me-statement to work i an excel sheet.
Is there a way make this work in excel, or is it not possible (which nothing is by the way.....)

Regards
Vidar

 

[rmikesmith]

Vidar,

Try something like this:

For count = 1 To 50
Me.OLEObjects("ComboBox" & count).Object.AddItem array(count)
Next

If you need to populate a ComboBox with multiple items you can use the following:

Me.OLEObjects("ComboBox1").Object.List = arrSource

where arrSource is an array you've already assigned values to; i.e. you don't need to loop.

Regards,
Mike

 

[SkipVought]

I'm guessing, that you could do this with ONE combobox per column, drastically decreasing your maintenance & coding.

BTW, have you considered using Data > Valdation - LIST? It's a basic, in-cell combo, without the control properties.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[vidar78]

Why I am using 50 comboboxes is a long story, and I don't want to go there, but thanks for great answers... I try it out, and let you know how it goes.

 

[ClulessChris]

How about using the controls collection, and testing for type?

Everybody is somebodys Nutter.

 

[vidar78]

SkipVought -> Using data/validation list is not an option.

CluelessChris -> I am not following you. I am using comboboxes from control toolbox, but what do you mean about "testing for type".

 

[ClulessChris]

Sorry, I was suggesting that you loop for each control in the controls collection, test for type and if combo box do your thing.
But can find no such collection for controls on a worksheet in vba.

Everybody is somebodys Nutter.

 

[ClulessChris]

You can however do a for each OLEObject and test for name.

Everybody is somebodys Nutter.