Fill a combobox from a dynamic list in a sheet

[davedave24]

Hi. I require to fill a combobox on a userform with a list of data in an Excel sheet that constantly has items added to it. For this reason I can't simply do .additem and list each one, or use the RowSource set to a high number as this will leave a huge white blank area at the bottom of the combobox.


I used to have a simple way of doing it but I can't remember - I usually set a blank cell out of the way somewhere to counta(a:a) - this counts the number of entries in the list, then I set up an array that gets all the data up to the number in that counta cell... then cycle through and add each item to the combobox

Thanks.

 

[SkipVought]

Hi,

Use a dynamic Named Range for the list and simply assign the Range Name to the ListFillRange. No VBA Required.

faq68-1331.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

If you know where it will start and the list has no breaks in it, then you can just read from the top down until you hit an empty cell. Something like

Public Sub loadFromColumn(startCell As Range)
  Dim val As Variant
  val = startCell.Value
  Me.cmboOne.Clear
  Do
   Me.cmboOne.AddItem (val)
   Set startCell = startCell.Offset(1, 0)
   val = startCell.Value
  Loop Until IsNull(startCell.Value) Or IsEmpty(startCell.Value)
End Sub

Private Sub UserForm_Activate()
  'pass in the beginning of the range
  loadFromColumn ActiveSheet.Range("A1")
End Sub

 

[davedave24]

Hi, is there a way to do that in Excel 2007? I can't make head or tail of the stupid new toolbar.

 

[davedave24]

Thank you MajP that works great.

 

[SkipVought]

Excel 2007...

Formulas > Defined Names > Define Name

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}