Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box
I have a Accounts sheet that contains the following:
Col-A = Last Name Col-B = First Name Col-C = M.I. I want to load a combo_box on the Invoice sheet with data from all 3 columns and up to 10 rows. Can you help me with the code. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box
maybe someone else has another way, but this is just using formulas.
i used a helper column to concatenate the names, column d in this instance. so, in a2: =B2&" "& C2 &" "&A2 and auto filled down then create a named range using a dynamic range: i named it employees and entered this in the refers to box: =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D),1) then in the combobox, i'm assuming it's on the sheet and not in a form, enter this into the input box on the control tab: employees -- Gary "Rick" wrote in message ... I have a Accounts sheet that contains the following: Col-A = Last Name Col-B = First Name Col-C = M.I. I want to load a combo_box on the Invoice sheet with data from all 3 columns and up to 10 rows. Can you help me with the code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box
forgot to mention, if you actually want code, just post back.
-- Gary "Rick" wrote in message ... I have a Accounts sheet that contains the following: Col-A = Last Name Col-B = First Name Col-C = M.I. I want to load a combo_box on the Invoice sheet with data from all 3 columns and up to 10 rows. Can you help me with the code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box
Rick,
You can place a ComboBox from the controls toolbox on your sheet,. Select design mode then right click, Properties In the Alphabetic list of Properties Depending on what you want to return from the selection set the BoundColumn 1 will be LastName Make ColumnCount 3 You can play with the ColumnWidth for each part of the name perhaps 50,15,50 LinkedCell wherever you want the result output to ListFillRange A1:C100 or however many ListRows 10 You can do this as code, as well but if this would work for you it is pretty simple. Scott On Thu, 21 Feb 2008 21:25:00 -0800, Rick wrote: I have a Accounts sheet that contains the following: Col-A = Last Name Col-B = First Name Col-C = M.I. I want to load a combo_box on the Invoice sheet with data from all 3 columns and up to 10 rows. Can you help me with the code. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box
If you are really looking for VBA code, give this a try...
Sub FillComboBox() Dim X As Long ComboBox1.Clear For X = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ComboBox1.AddItem Replace(Cells(X, "B").Value & " " & _ Cells(X, "C").Value & " " & _ Cells(X, "A").Value, " ", " ") Next End Sub The code assumes your names start in Row 2 (with Row 1 being the header row) and will fill down to the last name (using Column A). Rick "Rick" wrote in message ... I have a Accounts sheet that contains the following: Col-A = Last Name Col-B = First Name Col-C = M.I. I want to load a combo_box on the Invoice sheet with data from all 3 columns and up to 10 rows. Can you help me with the code. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box
Thanks to all of you, this helped to get me started.
"scott" wrote: Rick, You can place a ComboBox from the controls toolbox on your sheet,. Select design mode then right click, Properties In the Alphabetic list of Properties Depending on what you want to return from the selection set the BoundColumn 1 will be LastName Make ColumnCount 3 You can play with the ColumnWidth for each part of the name perhaps 50,15,50 LinkedCell wherever you want the result output to ListFillRange A1:C100 or however many ListRows 10 You can do this as code, as well but if this would work for you it is pretty simple. Scott On Thu, 21 Feb 2008 21:25:00 -0800, Rick wrote: I have a Accounts sheet that contains the following: Col-A = Last Name Col-B = First Name Col-C = M.I. I want to load a combo_box on the Invoice sheet with data from all 3 columns and up to 10 rows. Can you help me with the code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box vba that works like Forms Menu combo box | Excel Programming | |||
Excel VBA Combo Box Populating dependent on other combo box choices | Excel Programming | |||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes | Excel Programming | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) |