Populating multi-column combo box.

[mikedmitch]

I use the following code to populate a combo box in Word from an Excel spreadsheet. I want to use similar code to populate two columns in the combo box but cannot figure it out. Does anyone have some suggestions?

Private Sub UserForm_Initialize()
Dim wbData As Object
Dim rData
Dim rData1
Dim i

Set wbData = GetObject("C:\Book1.xls")
For Each rData In wbData.Sheets("Sheet1").Range("A1:A5")
ComboBox1.AddItem rData.Value
Next

End Sub

[gmaxey]

You should find some examples here: http://gregmaxey.mvps.org/word_tip_pages/populate_userform_listbox_or_combobox.html

[mikedmitch]

One of the codes seemed to work. The combo box populated, but I cannot see the data from the second column. Additionally, I need to know how to gather the data from the second column to put into my document. debug.print shows the value for the combo box to be what is in the first column.

Any ideas?

By the way, here is the code I got from you that I can get to work for me. I modified it a bit.


Private Sub Userform_Initialize()
'Late binding. No reference to Excel Object required.
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
'Open the spreadsheet to get data
Set xlWB = GetObject("C:\Book1.xls")
Set xlWS = xlWB.Worksheets(1)
cRows = xlWS.Range("A1:A5").Rows.Count - xlWS.Range("B1:B5").Row + 1
ComboBox1.ColumnCount = 2
'Populate the listbox.
With Me.ComboBox1
For i = 1 To cRows
'Use .AddItem property to add a new row for each record and populate column 0
.AddItem xlWS.Range("A1:A5").Cells(i, 1)
' .AddItem xlWS.Range("B1:B5").Cells(i, 2)
'Use .List method to populate the remaining columns
.List(.ListCount - 1, 1) = xlWS.Range("B1:B5").Cells(i, 2)
Next i
End With
'Clean up
Set xlWS = Nothing
Set xlWB = Nothing
' xlApp.Quit
' Set xlApp = Nothing
lbl_Exit:
Exit Sub
End Sub

[gmaxey]

You can populate multi-column listboxes using the .column and .list methods. You can return data from any or all columns:

Add two listboxes to a new userform and run:

Option Explicit
Private Sub ListBox1_Click()
'Return column 1
MsgBox Me.ListBox1.Column(0)
'Return column 1 & 2
MsgBox Me.ListBox1.Column(0) & " " & Me.ListBox1.Column(1)
End Sub
Private Sub ListBox2_Click()
'Return the hidden column
MsgBox Me.ListBox2.Column(1)
End Sub
Private Sub UserForm_Initialize()
Dim arrData_One(1, 3) As String
Dim arrData_Two(3, 1) As String
arrData_One(0, 0) = "A"
arrData_One(1, 0) = "Alpha"
arrData_One(0, 1) = "B"
arrData_One(1, 1) = "Bravo"
arrData_One(0, 2) = "C"
arrData_One(1, 2) = "Charlie"
arrData_One(0, 3) = "D"
arrData_One(1, 3) = "Delta"
With Me.ListBox1
.Column = arrData_One
.ColumnCount = UBound(arrData_One, 2)
'show both columns
.ColumnWidths = "50;50"
End With
arrData_Two(0, 0) = "W"
arrData_Two(0, 1) = "Whiskey"
arrData_Two(1, 0) = "X"
arrData_Two(1, 1) = "Xray"
arrData_Two(2, 0) = "Y"
arrData_Two(2, 1) = "Yankee"
arrData_Two(3, 0) = "Z"
arrData_Two(3, 1) = "Zulu"
With Me.ListBox2
.List = arrData_Two
.ColumnCount = UBound(arrData_Two)
'hide column 2
.ColumnWidths = "50;0"
End With
End Sub

[domfootwear]

I use the following code to populate a combo box in Word from an Excel spreadsheet. I want to use similar code to populate two columns in the combo box but cannot figure it out. Does anyone have some suggestions?

Private Sub UserForm_Initialize()
Dim wbData As Object
Dim rData
Dim rData1
Dim i

Set wbData = GetObject("C:\Book1.xls")
For Each rData In wbData.Sheets("Sheet1").Range("A1:A5")
ComboBox1.AddItem rData.Value
Next

End Sub
You can use ADO to do that, pls send att. file to here.

[mikedmitch]

This is part of the initialization file for my combo box. The spreadsheet I am using is in two possible locations depending on whether I am on the network or the stand-alone computer I use. I am populating two columns in my combo box. I use the data in the second column to populate a text box depending on the selection I make in the combo box.

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.DriveExists("K:\") = True Then 'The folder dosen't exists
Myxls = "K:\Info\Uncontrolled\Training\Operators.xls"
ElseIf fs.Folderexists("C:\Documents and Settings\examgroup\Application Data\Microsoft\Templates\") Then
Myxls = "C:\Documents and Settings\examgroup\Application Data\Microsoft\Templates\Operators.xls"
Else
MsgBox "Manual Entry of Names Required!"
Exit Sub
End If

'Open the spreadsheet to get data
Set xlWB = GetObject(Myxls)

Set xlWS = xlWB.Worksheets("JPMs")
cRows = xlWS.Range("A2:A100").Rows.Count - xlWS.Range("B2:B100").Row + 1
'Populate SJN1 combobox
SJN1.ColumnCount = 2
'Populate the listbox.
With SJN1
For i = 1 To cRows
rData = xlWS.Range("A2:A100").Cells(i, 1)
If rData = "" Then
'Skip empty data cells
GoTo SkipSJN1
Else
'Use .AddItem property to add a new row for each record and populate column 0
.AddItem rData
'Use .List method to populate the remaining columns
.List(.ListCount - 1, 1) = xlWS.Range("A2:A100").Cells(i, 2)
End If
Next i
End With
SkipSJN1:



This is the second part of my code where I use the selections I make in the user form to update the information in the base document.

Private Sub SJN1_AfterUpdate()
ActiveDocument.CustomDocumentProperties("SJN1").Value = SJN1.Value
Debug.Print SJN1.Value
ListIndex = SJN1.ListIndex()
SJT1.Value = SJN1.List(ListIndex, 1)
End Sub

Thanks for all your help.

[gmaxey]

Is there a question here or are you just offering your solution?