Solved: Combo Box: set possible values 1 thru N in code

[GreenTree]

Hello all!

I have a userform that can select from a large number (several hundred) of items, and depending on other factors, the number of items on that list can change. Each item is simply a whole number 1 thru (max item #). I'd like to use a combo box that the user can use to select any valid item #; is there a way to tell the combo box that it should offer values of 1 thru N?

I considered the add item method, but it seems inefficient to do a for-next loop to add a couple hundred items to the combo box, then clear it when the list of items changes (can change up or down). I guess I could just put a range of numbers someplace in a spreadsheet & tell the combo box the range, but that also seems inefficient to have a few hundred cells devoted to nothing but numbers 1 thru a couple hundred.

So... is there an efficient method I'm missing, or should I just do the for-next loop & add items each time?

Many thanks,

G.T.

[Bob Phillips]

Is loading an array and pointing that at the combob any better in your view?

[mdmackillop]

You could add the numbers to the sheet then clear them
[vba]Private Sub UserForm_Initialize()
Dim Rng As Range
Set Rng = Range("Z1:Z100")
Rng = "=Row()"
Me.ComboBox1.List() = Rng.Value
Rng.ClearContents
End Sub

[/vba]

[GreenTree]

Thanks, Malcolm. I'll go that route, setting the range to however far down the list of numbers I need & pointing the combo box at that range. As often as I'll be resetting the range, I will probably just devote a column to numbers, and not bother clearing & re-setting them every time. Cells are cheap; why spend the cycles?

Is loading an array and pointing that at the combob any better in your view?

I'm still new enough at this that I'm not sure my view has much credential; doing a for-next loop to add items seems (and I'm perfectly willing to be corrected here) like a lot of cycles to go thru; setting the size of the range looks like the fewest lines of code, which is an imperfect proxy for "efficient solution." As little as I know about VBA, I'd think I'd need a loop of some sort to set the values 1 thru 344 (or whatever) in the 344 elements of an array, so based on a limited knowledge, I'd say that the array doesn't seem to be an efficient way to go either. But again, it wouldn't surprise me to find that there's a far more elegant way to go about it that I'm not considering.

I've had experience (and actual training) in Basic, Pascal, and Ada, although nothing more recent than about 1989. Picking up VBA goes fairly quickly in some things, not at all so fast in others, and I regularly find an elegant way of doing something that simply would not have occurred to me. In a worksheet, setting .value = .value instead of doing copy/paste special / values is one example; there are plenty more.

Anyway, I wondered if there was a means I couldn't see to tell the combo box to display values 1 thru N. Malcolm's answer is good, and that being a quick & easy solution, tells me all I need to know. (i.e. no, there is no property to set .abc to true and then setting .xyz = 412 gives you the 1-412 range you're looking for).

If the array approach would be better, I'd be interested to see it; is that the way you'd set the list of the combo box, Bob?

Thanks,

G.T.

[johnske]

or maybe the RowSource property...
If you want the entire column,
[vba] With Sheets("DATA")
.Activate
ComboBox1.RowSource = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Address
End With[/vba]
If you only want part of the column
[vba]With Sheets("DATA")
.Activate
ComboBox1.RowSource = .Range("A2:A12").Address
End With[/vba]

[mdmackillop]

I don't believe it will make a difference with a few hundered rows, but saving 1 to 65536 in as column increases the workbook size from 30kb to 3.5mb. There seems no noticable time difference (0.26 sec v 0.13 sec) in writing/deleting the values as against reading them from a saved column.

[Bob Phillips]

As little as I know about VBA, I'd think I'd need a loop of some sort to set the values 1 thru 344 (or whatever) in the 344 elements of an array, so based on a limited knowledge, I'd say that the array doesn't seem to be an efficient way to go either.

344 items in such a loop is almost irrelevant, it is tiny.

[mdmackillop]

Looping 344 items timed at 0.016 sec.

[Bob Phillips]

Okay, so I'm wrong! Didn't realise it would take that long.

[mdmackillop]

Maybe the timer is causing the extended delay. Now if only we could time without the timer!

[Bob Phillips]

http://ccrp.mvps.org/index.html?controls/ccrptimer6.htm

[mdmackillop]

Thanks Bob,
I'll have a look at this.