2-column combo box via code...
 

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

2-column combo box via code...
 

You have to set the combobox ColumnCount property to 2.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"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

2-column combo box via code...
 

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

2-column combo box via code...
 

Tom,

I saw, judging by your code, that my main mistake for not getting the 2nd
column was that I was adding values (0,1) and (0,2) in the ComboBox.AddItem,
whereas I should be using (0,0) and (0,1).

Array indexing starts with 0, unless otherwise suggested (Base=1). Right?

Thanks [again] for everything!!

2-column combo box via code...
 

doesn't have anything to do with array index per se. Column numbering and
row numbering in the list starts with zero. Option Base 1 wouldn't change
that, so that is why I said it isn't array indexing per se.

--
Regards,
Tom Ogilvy



"DoctorG" wrote in message
...
Tom,

I saw, judging by your code, that my main mistake for not getting the 2nd
column was that I was adding values (0,1) and (0,2) in the
ComboBox.AddItem,
whereas I should be using (0,0) and (0,1).

Array indexing starts with 0, unless otherwise suggested (Base=1). Right?

Thanks [again] for everything!!