Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box Code | Excel Discussion (Misc queries) | |||
Combo Box code | Excel Programming | |||
code for combo box | Excel Programming | |||
Combo Box Code... | Excel Programming | |||
Combo Box code | Excel Programming |