Create List for ComboBoxes

  • Thread starter Thread starter Benjamin
  • Start date Start date
B

Benjamin

I need to create a dropdown combobox that has the names from Columb B in
Sheet "Names"

The combobox is in Form FrmMTCLog
the drop down is: combobox1
I need the combobox1 to have a list of all the names from Sheet"Names" Cell
b2 and down to the last filled in Column.
 
One way:

In the _initialize procedure:

with worksheets("Names")
me.combobox1.list = .range("B2",.cells(.rows.count,"B").end(xlup)).value
end with

(last filled in column B, right???)
 
Private Sub UserForm_Initialize()
rng = Sheets("Names").Cells(Rows.Count, 2).End(xlUp).Address
Me.FrmMTCLog.RowSource = "Names!$B$2:" & rng
End Sub
 
Easiest way is to do some work on the sheet first.

============
CREATE A DYNAMIC NAMED RANGE OF OPTIONS
1) Open the Named Range box (Insert > Name > Define)
2) In the "Names in Workbook" line, type in a name...for instance OPTIONS
3) At the bottom in the "Refers To" line, enter this dynamic formula:

=INDEX(Sheet2!$B:$B, 2):INDEX(Sheet2!$B:$B, COUNTIF(Sheet2!$B:$B,">"""))

4) Click ADD, then Close

You now have a named range called "Options" the keeps itself expanded to
include all the items in Sheet2, range B2>bottom of that data range. You
won't ever have to edit it.

============
CONNECT YOUR COMBOBOX TO THE NAMED RANGE
1) Right-Click on your combobox and select PROPERTIES
2) In the LISTFILLRANGE enter the value of OPTIONS


Your combobox is now linked permanently to the dynamic range "Options".

Does that help?
 
Using the .rowsource property is another way.

But I would use something like:

Private Sub UserForm_Initialize()
Dim rng as Range

with worksheets("Names")
set rng = .range("B2",.Cells(.Rows.Count, "B").End(xlUp))
end with

'I think the form was named FrmMTCLog.
me.combobox1.RowSource = rng.address(external:=true)

End Sub

Then if the worksheet name changed, I'd only have one spot to fix (or use the
Codename for that sheet and not have to worry???).
 
Back
Top