PDA

View Full Version : [SOLVED] Dynamic Combo Box



Sir Phoenix
09-16-2005, 01:07 PM
I have a combobox on a userform that I want to use values based upon how many records are in. (Records populate starting at A2 and go down.)

If you enter in a new entry that hasn't been used yet, I want the combobox to dynamically add the new entry to the dropdown list.

mvidas
09-16-2005, 01:33 PM
Hi Sir Phoenix, thanks for coming to VBAX! Hope it is working out for you so far!

One way to do it is to enter something like the following into your userform_activate (or _initialize perhaps, up to you) event:


Dim CBValues() As Variant
With ThisWorkbook.Sheets(1) 'change as needed
CBValues = .Range("A2", .Range("A65536").End(xlUp)).Value
End With
ComboBox1.List = CBValues

This will create an array containing those cells' values, and will fill the combo box each time. Seems to me to be the easiest/most efficient way to do that.

Matt

Sir Phoenix
09-16-2005, 01:49 PM
That works! Thanks for the help. I'm trying to learn more VB, so I'll mark this one solved a little later on to see some other approaches.

Sir Phoenix
09-16-2005, 02:57 PM
Alright, the last suggestion works, but I get a Run-time error '381':

Could not set the List property. Invalid property array index.

Whenever the list size is 1. Is there a solution that also allows for 1 or 0 entries?

Glaswegian
09-16-2005, 04:05 PM
I tend to do this slightly differently to the way Matt suggested. I define the range, then name the range, then set the rowsource to the named range. Works fine each time I load the form.


Dim Rng1 As Range
Set Rng1 = Sheets("Main").Range("A2", Range("A65536").End(xlUp))
Rng1.Name = "New Range"
Combobox1.RowSource = "New Range"

HTH

Regards

Sir Phoenix
09-16-2005, 04:14 PM
That worked. Thanks!