Private Sub CommandButton1_Click()
Dim Cell As Range
Dim Er As MSForms.ComboBox

Set Er = Sheet10.Ergo_Combo
With Er
.Clear
.ColumnCount = 2
.AddItem
.List(0, 0) = "Code"
.List(0, 1) = "Description"
End With

For Each Cell In Range("D_Projects").Columns(2).Cells
If Cell.Value = Range("C_Rep_Customer_Code").Value Then
Er.AddItem Cell.Offset(0, 0).Value
Er.List(Er.ListCount - 1, 1) = _
Cell.Offset(0, 1).Value
End If
Next

If Er.ListCount = 1 Then
MsgBox ("No entries")
Er.Enabled = False
Else
Er.Enabled = True
End If
End Sub


--
Regards,
Tom Ogilvy


"DoctorG" wrote in message
...
I followed Tom Ogilvy's suggestions in another thread and set up a
1-column
combo that works fine. Now I'm trying to modify the code for 2 columns but
with no luck. I only get 1 column of data in the combo.

The case is as follows : I need to "read" range "D_Projects" and load
"Sheet10.Ergo_Combo" whenever column2 of D_Projects is equal to
"C_Rep_Customer_Code" with a Code and a Description for the user to choose
from.

Please take a look at the code and tell me what is wrong.

Bear in mind that I'm trying to set up the first row as titles / headings.

If it's not too much please advise on how I can replace the full reference
to
Sheet10.Ergo_Combo with a short local object (as is the case with "Cell"),
since
D_Projects and Ergo_Combo are on different sheets and I need full
reference.

Thanks for your help
.........................................
Dim Cell As Range

Sheet10.Ergo_Combo.Clear
Sheet10.Ergo_Combo.ColumnCount = 2
Sheet10.Ergo_Combo.AddItem
Sheet10.Ergo_Combo.List(0, 1) = "Code"
Sheet10.Ergo_Combo.List(0, 2) = "Description"

For Each Cell In Range("D_Projects").Columns(2).Cells
If Cell.Value = Range("C_Rep_Customer_Code").Value Then
Sheet10.Ergo_Combo.AddItem Cell.Offset(0, 3).Value
Sheet10.Ergo_Combo.List(Sheet10.Ergo_Combo.ListCou nt - 1, 2) =
Cell.Offset(0, 1).Value
End If
Next

If Sheet10.Ergo_Combo.ListCount = 1 Then
MsgBox ("No entries")
Sheet10.Ergo_Combo.Enabled = False
Else
Sheet10.Ergo_Combo.Enabled = True
End If