How do I loop through combobox objects to add items?

[bodhi808]

Hi

I'm a pretty basic user of VBA and am struggling with the method of looping through objects to make changes.

I have 12 comboboxes on a userform which I have set to dropdown list and want to populate during the userform initialization.
Each combobox is called "pt*" where * is a number from 1 - 12.
I have currently set up my code with 12 lines, one for each combobox to apply the same dropdown list to each of the 12 boxes.
ie

 pt1.rowsource = ptsource

(where ptsource contains a range reference on the worksheet)

I'm interested to learn how to improve my code to loop through 1-12 and apply the rowsource to each combobox.

I did search through some past questions but could only come up with a method which seemed to step through every combobox on a worksheet rather than userform and in my case, I have two other comboboxes on the userform which I do not want to add this rowsource to, only the "pt*" named comboboxes.

Would really appreciate some help to understand how to do this as I'm interested to keep improving any code I write.

Many thanks
Chris

 

[bodhi808]

I've found the answer to this now.
Thanks

 

[RoryA]

FYI, I don't recommend using Rowsource at all. I would use something like:

Dim n as long
Dim Data
Data = Range(ptsource).value
for n = 1 to 12
me.controls("pt" & n).List = Data
next n

 

[bodhi808]

Firstly, let me apologise, it seems I've unknowingly committed a bit of a posting no no.
I cross posted on another excel forum, but every day is a school day and I've read the guide and understand my mistake.
So, for clarity, my question was asked and answered here. [SOLVED] How do I loop through combobox objects to add items?

RoryA thank you very much for your reply, it's appreciated. I'm curious to know why the rowsource method is not recommended in favour of list?
I've only got an iPhone here at the moment so can't test it, but will this work where ptsource is multiple cells rather than a single cell? I will certainly test it when I'm back at my pc.

Thanks again and apologies for the rookie posting error.

 

[RoryA]

Yes, it will work with multiple cells (not a lot pf point assigning one cell to a combobox )
I don't like to bind controls to cells (using Rowsource or controlsource) because changes on the sheet, or recalculations can unexpectedly trigger events on your userform. I much prefer to be in control of when my userform does anything!