Hi,

I am currently working in Word 2003 VBA UserForms.  I could use a
little help populating my multi list combo box with my DAO data
(Excel).  I have the code, thanks to the help of these groups, for
populating a combo box.  However I want to populate both a combo box
and a multi list box differently.

cmbAuthorsInitials - This combo box is linked to the DAO data in
column 1. (In a memo this gets the Authors information from the Excel
table).  This part works fine.

lstTo -  I want this multi list combo box to display a different
column of data from the same DAO source as the cmbAuthorsInitinals .
(This will list the recipients the memo is going to).

I tried to populate both boxes with the same Load instructions but I
couldn't get it to work.  So I had the cmbAuthorsInitials box fill in
to the lstTo box.  This worked but the only problem is I want it to
list the Authors Name from column2 not column1.

Maybe there is a better way to go about this.  I have never done the
list boxes before so any help would be greatly appreciated.

Kerri

CODE:
Private Sub UserForm_Initialize()

'=====Load Authors data from Excel table=====
 'Contains a list of Authors and preferences
 'NOTE: Must Reference "Microsoft DAO 3.6 Object Library" by going to
"Tools | References..." for Word 2003
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim NoOfRecords As Long

   ' Open the database
   Set db = OpenDatabase("c:\_Auth\AuthList.xls", False, False,
"Excel 8.0")

   ' Retrieve the recordset
   Set rs = db.OpenRecordset("SELECT * FROM `Authors`") '**Authors is
Named Range in the Excel Spreadsheet.

   ' Determine the number of retrieved records
   With rs
        .MoveLast
        NoOfRecords = .RecordCount
        .MoveFirst
   End With

   ' Set the number of Columns = number of Fields in recordset
   cmbAuthorsInitials.ColumnCount = rs.Fields.Count
''    lstTo.ColumnCount = rs.Fields.Count '*

   ' Load the ListBox with the retrieved records
   cmbAuthorsInitials.Column = rs.GetRows(NoOfRecords)
   'added to also fill in the Listbox to field
''   lstTo.List = rs.GetRows(NoOfRecords) '*

   ' Cleanup
   rs.Close
   db.Close

   Set rs = Nothing
   Set db = Nothing
'=======End Excel Datafile=================

lstTo.List = cmbAuthorsInitials.List   '* set to name (col. 2) and not
initials?

End Sub

Private Sub cmdOK_Click()

'* lstTo Listbox results create an array to get contents of each row.
   Dim strTo As String
   strTo = cmbTo
       For i = 1 To lstTo.ListCount
           If lstTo.Selected(i - 1) = True Then
               strTo = strTo & ", " & lstTo.List(i - 1)
           End If
       Next i
   UpdateBookmarkText .Bookmarks("To"), strTo

'Is there a way to make an "and" appear before the last name from the
multi list?

End Sub