"Sources" for Userform ListBox or ComboBox choices

[Ridge Kennedy]

Dear All,

Working in Office 2003 on XP workstations, I'd like to be able to offer
users of a new template a dropdown choice of "authors" for letters via a
userform. The goal would be to allow them to choose a name and the list
would populate the letter with full name and title.

What would be the most maintainable way to link a rowsource to the listbox
or combobox controls that Word offers? We are now distributing templates via
login script, so ideally it wants to be something that can be done that way.
We might have 10 or 12 people maximum would we would need names and titles
for.

General pointers in the right direction gratefully appreciated.

Sincerely,

Ridge (in New Joisey)

 

[Doug Robbins - Word MVP]

Take your pick from the following three methods using a Word document, a
table in Access or a named range in Excel as the source.

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

The following populates the list box with data from an Access database
table:

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM Owners")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

To use Excel as the datasource, See the article "Load a ListBox from a Named
Range in Excel using DAO" at:

http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Ridge Kennedy]

Doug said:

Hope this helps.

It must get really boring, having people say "thank you" again and again and
again and . . .

Thank you.

R.

 

[MariaV]

Hi Doug,
Your info has helped & it was much easier to understand then most I have
read.

I am still a little confused because when I run the code it gives me a macro
dialog box. I wanted to populate a drop down box with our customers name,
but this drop down box will repeat 25+ times in the same document (its a form
for our call center to record who's call is open or closed). Someone other
than myself will maintain the list, so I need to keep it 'easy' for them. Do
you think I should embed the list directly in the code?

Thanks in advance for your help.

Maria

 

[Doug Robbins - Word MVP]

If the same selection from the combobox is being used 25 times, then I would
be creating a document variable the value of which is set to the selection
from the combobox and in the template, I would use { DOCVARIABLE } fields
whereever the information was required to appear. You users will go crazy
having to select the same thing 25 different times.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[MariaV]

Thanks for getting back to me.

It would most likely be a different client in each row . . . they would just
have the same choices in each combo box (which would be a list of our
clients).

Maria