PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average.

Populating a ComboBox from VBA with 2 columns

 
 
df
Guest
Posts: n/a
 
      8th Jul 2003
Hi I need to populate an excel combo box with recordset
fields i already retrieve from an SQL Database. I need to
columnes code and description. For example: Employee ID
and Employee Lastname. Y need to show the Lastname in the
combo and retrive the selected Employee ID from it.

I tried
ComboBox.AddItem()

But I could only add an item with one field. How can I add
the couple Employee ID - LastName? (I discard the
concatenation method).

Regards
Daniel
 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      9th Jul 2003
If those values are on a worksheet in adjacent columns, you could pick up that
range and dump it into an array. Then use the array to fill the combobox:

Option Explicit
Private Sub UserForm_Initialize()

Dim iRow As Long
Dim myArray As Variant

myArray = Worksheets("sheet1").Range("a2:b20")

With Me.ComboBox1
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ".5 in; .5 in"
.List = myArray
End With

End Sub

Since I set the boundcolumn to 2, I get the second column returned. (You could
even hide the second column by using: .ColumnWidths = ".5 in; .0 in"

But the value is determined by the .boundcolumn.

Private Sub CommandButton1_Click()
MsgBox Me.ComboBox1.Value
End Sub

If you can't pick it up nicely, you can add them one at a time:

Private Sub UserForm_Initialize()

Dim iRow As Long
Dim myCell As Range

With Me.ComboBox1
.ColumnCount = 2
.BoundColumn = 2
For iRow = 1 To 20 step 3
Set myCell = Worksheets("sheet1").Cells(iRow, 1)
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value
Next iRow
End With

End Sub

Another option may be to just put the one column in and then use some kind of
lookup to determine the other value.




df wrote:
>
> Hi I need to populate an excel combo box with recordset
> fields i already retrieve from an SQL Database. I need to
> columnes code and description. For example: Employee ID
> and Employee Lastname. Y need to show the Lastname in the
> combo and retrive the selected Employee ID from it.
>
> I tried
> ComboBox.AddItem()
>
> But I could only add an item with one field. How can I add
> the couple Employee ID - LastName? (I discard the
> concatenation method).
>
> Regards
> Daniel


--

Dave Peterson
(E-Mail Removed)
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
populating a combobox using vba ArielZusya Microsoft Access Form Coding 4 16th Sep 2008 07:47 PM
Populating Junction tables and/or populating two tables at once =?Utf-8?B?Q2hlZXNlX3doaXo=?= Microsoft Access Getting Started 2 4th Oct 2006 03:56 PM
HowTo? shift focus in VBA IDE between Object combobox, procedure combobox, and Code window Malcolm Cook Microsoft Access 0 11th Oct 2005 03:42 PM
Populating only one text box instead of populating another text box Alex Martinez Microsoft Access Form Coding 1 30th Jul 2005 09:44 AM
Populating combobox from another combobox David Goodall Microsoft Excel Programming 1 12th Sep 2004 03:42 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:18 AM.