Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create List for ComboBoxes
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create List for ComboBoxes
One way:
In the _initialize procedu with worksheets("Names") me.combobox1.list = .range("B2",.cells(.rows.count,"B").end(xlup)).val ue end with (last filled in column B, right???) Benjamin wrote: 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. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create List for ComboBoxes
Private Sub UserForm_Initialize()
rng = Sheets("Names").Cells(Rows.Count, 2).End(xlUp).Address Me.FrmMTCLog.RowSource = "Names!$B$2:" & rng End Sub "Dave Peterson" wrote in message ... One way: In the _initialize procedu with worksheets("Names") me.combobox1.list = .range("B2",.cells(.rows.count,"B").end(xlup)).val ue end with (last filled in column B, right???) Benjamin wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create List for ComboBoxes
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 B2bottom 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? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Benjamin" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create List for ComboBoxes
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???). JLGWhiz wrote: Private Sub UserForm_Initialize() rng = Sheets("Names").Cells(Rows.Count, 2).End(xlUp).Address Me.FrmMTCLog.RowSource = "Names!$B$2:" & rng End Sub "Dave Peterson" wrote in message ... One way: In the _initialize procedu with worksheets("Names") me.combobox1.list = .range("B2",.cells(.rows.count,"B").end(xlup)).val ue end with (last filled in column B, right???) Benjamin wrote: 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. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create dynamic comboboxes | Excel Programming | |||
Help needed - populating comboboxes from list?? | Excel Programming | |||
Help needed - populating comboboxes from list?? | Excel Programming | |||
How to create adress list so can mail merge and create labels? | Excel Discussion (Misc queries) | |||
Want to Create a List in Excel 2002; Don't see List in Data Menu? | Excel Discussion (Misc queries) |