Combo box

[Rick]

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.

 

[Gary Keramidas]

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 Keramidas]

forgot to mention, if you actually want code, just post back.

 

[scott]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

Thanks to all of you, this helped to get me started.