Greetings all,
Here's the scenario. I've created a Word document for my clinicians
with a listbox which contains diagnosis codes. I've found that the
limit for items in the  listbox/combo box in Word 2000 is 25 items.
Can a file be created with an *.xls, or *.dbf or *.txt extension that
will store values for this? Or is creating this in VBA the answer?

If creating in VBA is the workaround for this,  I would like to have
the form which contains the listbox to "show" when the cursor moves
into the bookmark field that I'll set up to hold the value of the
listbox? Is this possible?

TIA
Jim Adams
Maysville KY  USA

Hi Jim,

I take that you are using formfields in a protected document.  That being
the case, you could put a text formfield where you want the information to
appear, then have a macro run on entry to that field.  The macro would cause
a userform to be displayed and on that userform, you would have a listbox
control.  The initialise event for the userform would populate the listbox
with the data from your external file, and you could have a doubleclick
event attached to the listbox that would take the selected entry and set it
as the result of the formfield in the document and close the userform.

If you are not familiar with UserForms, take a look at the article "How to
create a Userform" at

http://www.mvps.org/word/FAQs/Userforms/CreateAUserForm.htm

The macro that runs on entry to the formfield will need to contain the
following code

UserForm1.Show

Here is some code from a UserForm that I created for someone else, that
populates a series of list boxes on that form with data that was stored in
tables in a separate Word document:

Private Sub UserForm_Initialize()

'   This code populates the Company, Weight, Product, Finish, Use, Color and
Size comboboxes
'   with the items contained in the relevant tables in the document
Suppliers.doc
RequestDate.Text = Format(Date, "MMMM dd, yyyy")
Dim sourcedoc As Document, i As Long, myitem As Range
' Modify the path in the following line so that it matches where you saved
Suppliers.doc
Set sourcedoc = Documents.Open(FileName:="d:\worddocs\Suppliers.doc")
For i = 2 To sourcedoc.Tables(1).Rows.Count
    Set myitem = sourcedoc.Tables(1).Cell(i, 1).Range
    myitem.End = myitem.End - 1
    Company.ADDITEM myitem.Text
Next i
For i = 2 To sourcedoc.Tables(2).Rows.Count
    Set myitem = sourcedoc.Tables(2).Cell(i, 1).Range
    myitem.End = myitem.End - 1
    Weight.ADDITEM myitem.Text
Next i
For i = 2 To sourcedoc.Tables(3).Rows.Count
    Set myitem = sourcedoc.Tables(3).Cell(i, 1).Range
    myitem.End = myitem.End - 1
    Product.ADDITEM myitem.Text
Next i
For i = 2 To sourcedoc.Tables(4).Rows.Count
    Set myitem = sourcedoc.Tables(4).Cell(i, 1).Range
    myitem.End = myitem.End - 1
    Finish.ADDITEM myitem.Text
Next i
For i = 2 To sourcedoc.Tables(5).Rows.Count
    Set myitem = sourcedoc.Tables(5).Cell(i, 1).Range
    myitem.End = myitem.End - 1
    Use.ADDITEM myitem.Text
Next i
For i = 2 To sourcedoc.Tables(6).Rows.Count
    Set myitem = sourcedoc.Tables(6).Cell(i, 1).Range
    myitem.End = myitem.End - 1
    Colour.ADDITEM myitem.Text
Next i
For i = 2 To sourcedoc.Tables(7).Rows.Count
    Set myitem = sourcedoc.Tables(7).Cell(i, 1).Range
    myitem.End = myitem.End - 1
    Size.ADDITEM myitem.Text
Next i
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

The code for the doubleclick event of the listbox will be

ActiveDocument.FormFields("formfieldname").Result = ListBox1.Text

I hope that's enough to get you started.   Please post any response or
follow up to the newsgroups for the benefit of others who may also be
following the thread.

Hope this helps,
Doug Robbins - Word MVP

        The way I do this is to create a form with a listbox control on it.  The listbox
is populated programmatically by a DAO call using a SQL query.  The data is stored in
an Access database, but there are many other ways of doing this depending on what
other technology you have available.
        When the user selects a record in the list box, you have several options.  The
user can just double-click and a resulting action occurs, or the user can click a
button such as "Insert" or "Edit" or "Delete".
        I'm not clear on what you're describing in the second paragraph, but I can
provide details on how to do the above if you wish.
        Stephen

Doug and Stephen,
Thanks for your quick replies. Since I only have 30-35 items to place
in the listbox, it is probably better to go ahead and include this in
my code for a userform.  I was interested in accessing a file so that
I wouldn't have to update every clinician's copy of the Word document
when the values change. I could simply update the data file on the
server.
.
However, Stephen, I am interested in learning to use the DAO/SQL query
techniques. From what you're saying, I could save a comma delimited
file as a DBF file or I guess use an Excel SS for my data source. And
of course I could create the file in Access and query the MDB file.

You can either post your info here in the newsgroup or email me. I
have some knowledge of SQL, Access2000, and learning more about DAO,
but in relation to Access and not Word2000.

TIA
Jim Adams
Maysville, KY  USA

        WARNING: You're best off copying all the below into a word processor and setting
your font to a small-point fixed font like Courier so it's easier to read.

        Okay, here's a typical example ... I want to pop up a form that shows a list of
employees.  The user clicks on a name to select it and perform some function.
(Although they could double-click it which would be a typical Windows event, I find a
lot of users aren't that handy with a mouse, so I have them click once on the name
then click a button below like "OK" or "Edit" or "Delete" or whatever.)  The list of
names in the list box comes from a table in an Access database.
        One scenario where we use this is in a clerical pool.  They generate a
pre-programmed letter then select from the list box the name of the employee whose
signature will appear at the bottom of the letter.  The list box displays only last
name and first name in the format "Doe, John" but when the user clicks "OK" it
retrieves from the database other fields, such as job title, phone number, fax
number, signature initials, etc.
        First, let's create our database.  We'll call it Employees and store it on a
server in the folder P:\DBS.  (P: being a mapped drive on the file server.)  In the
Employees database, we'll create a table called tblEmployees.  The tblEmployees table
has these fields:

        AutoID          'In this example, we'll use an AutoNumber assigned by Access
        LastName
        FirstName
        Signature       '(for example, I sign "Stephen C. Smith")
        Title
        Initials
        Phone
        Fax

        Now, for the Word template ... I create a module called "Macros" but there are
many different ways to do this.  I've seen one example where they used the Word
object "ThisDocument" to store the code.
        In the Macros module, I start by creating some global variables.  Mine are:

        Public db As Database
        Public rs As Recordset
        Public i As Integer
        Public iAutoID As Integer
        Public iChoice as Integer
        Public strName As String
        Public strSQL As String

        I also create a global constant for the database itself:

        Public Const cDb As String = "P:\DBS\Employees.mdb"

        This makes it easier to port the code to other templates or other applications;
I just change the value of cDb to whatever database has the data being used to
populate the list box.
        Then we create the UserForm, which I'll call frmEmployees.  Place on
frmEmployees a list box we'll call lstEmployees.  The lstEmployees object has these
properties:

        BoundColumn = 1         'The first column will be the unique AutoID used in the
table to ID the record
        ColumnCount = 2         'Column 1 will be the AutoID; Column 2 will be a
concatenation
        ColumnHeads = False     'True would display the field names and we don't want that!
        ColumnWidths = 0 pt; 150 pt.    'Column 1 is 0 pt. because we don't want to
display
                                        'the Auto ID, just the name.  You'll probably have to play
                                        'with the Column 2 width to see what works for you.

        Here's the code that I place in the UserForm_Initialize event:

        '--------------------------------------------------------------------

        Private Sub UserForm_Initialize()

        'This will hold the array of values to be displayed.
        Dim RepArray()

        Set db = OpenDatabase(cDb, False, True)

        'Define the SQL string to be used in retrieving the recordset.
        strSQL = "SELECT AutoID, LastName, FirstName FROM tblEmployees ORDER BY
LastName, FirstName"

        'Open the recordset.
        Set rs = db.OpenRecordset(strSQL)

        'Redim the array with the values retrieved by the recordset.
        ReDim RepArray((rs.RecordCount - 1), 2)

        'Populate the array with records for the lstEmployees list box.
        'iAutoID holds the AutoNumber (unique ID).
        'strName is a concatenation of the Last Name and First Name fields.
        For i = 0 To (rs.RecordCount - 1)
                iAutoID = rs.Fields("AutoID")
                strName = rs.Fields("LastName") & ", " & rs.Fields("FirstName")
                RepArray(i, 0) = iAutoID
                RepArray(i, 1) = Trim(strName)
                rs.MoveNext
        Next i

        'Populate the lstEmployees list box with the RepArray values.
        frmEmployees.lstEmployees.List() = RepArray

        'Set the ListIndex to 0 so no one name is a default.
        frmEmployees.lstEmployees.ListIndex = 0

        'Clear the DAO variables.
        Set db = Nothing
        Set rs = Nothing

        End Sub

        '--------------------------------------------------------------------

        *** ONE BIG TRAP -- Make sure that in Tools References you set a reference to
your Microsoft DAO Object Library.  It should be listed on the Tools References list
of available references.  If not, search your computer for a file named DAO350.DLL or
similar.  (Search for DAO*.DLL ...)  Our inept I.S. department sometimes thinks it's
being clever by not doing a complete Office install and they leave off DLL's.

        To determine which record the user selected, I place this code in the Click
event of the button they select (e.g. "OK"):

        '--------------------------------------------------------------------

        Private Sub cmdOK_Click()

        'Column 1 has the AutoID number, so that's the value we use to retrieve
        'a record from the database.
        frmEmployees.lstEmployees.TextColumn = 1
        'Assign the .Text property to iChoice, a global variable.
        iChoice = frmEmployees.lstEmployees.Text

        End Sub

        '--------------------------------------------------------------------

        From here, you can do pretty much anything you want because the global variable
iChoice contains the AutoID for the selected record.  For example, you could retrieve
the record from the database with code similar to this:

        '--------------------------------------------------------------------

        Set db = OpenDatabase(cDb, False, True)

        'Define the SQL string to be used in retrieving the recordset.
        strSQL = "SELECT LastName, FirstName, Signature, Title, Initials, Phone, Fax " &
_
                "FROM tblEmployees WHERE AutoID = " & iChoice

        'Open the recordset.
        Set rs = db.OpenRecordset(strSQL)

        '--------------------------------------------------------------------

        You could even delete the selected record:

        '--------------------------------------------------------------------

        Set db = OpenDatabase(cDb, False, True)

        'Define the SQL string to delete the record
        strSQL = "DELETE * FROM tblEmployees WHERE AutoID = " & iChoice

        'Execute the SQL string
        db.Execute strSQL

        '--------------------------------------------------------------------

        There are a lot of different ways to accomplish all the above, but this works
for me.  I'm sure more experienced members of this newsgroup can suggest other ways
to do it.  We used to use ODBC but this is faster and much easier to configure.
        Stephen

Hi,

The foolowing code will load the contents of a plain text file into
ListBox1.

Public Sub ListBox1Populate()

Dim MyListBox As MSforms.ListBox
Dim MyArray() As String
Dim MyTextFile As String
Dim FileNumber As Integer
Dim x As Integer
Dim FileExist As String

MyTextFile = "c:\MyFolder\MyText.txt"
FileNumber = FreeFile

FileExist = Dir(MyTextFile)
If FileExist = "" Then
    MsgBox "File not found."
    Exit Sub
End If

FileNumber = FreeFile
Open MyTextFile For Input As #FileNumber
    While Not EOF(1)
        ReDim Preserve MyArray(x + 1)
        Line Input #FileNumber, MyArray(x)
        x = x + 1
    Wend
Close #FileNumber

Load UserForm1
UserForm1.ListBox1.List = MyArray
UserForm1.Show

End Sub

        Of course, whether you use a text file or a database depends on the
circumstances.  In general I prefer to use a database source because the data is
dynamic.  Any changes are automatically reflected.  If the text file is generated
from another source, e.g. a mainframe download, then the data might be obsolete.
        Another advantage of a database approach is that you can store many records of
data in one place.  For example, I've used one Access database to contain tables with
standard lists of city names, states, state postal codes, and country names.  If
placed out on a server, not only can it be a source for a list box but other
databases can link to it as a central repository of standardized data.
        But like I said, it depends on the circumstances.
        Stephen