[force530]

The data would be from a list or wherever would make the process easier. I
know in Excel you can create a ref list ... I dont know how to do this in
Word (other than a dropdown, which wont work), despite researching. The
reference data would be entered only once and may occasionally need updating.
The number of names would be somewhere between 15-25, with the associated
addresses, city, state.




"Greg Maxey" wrote:

force530,

This is entirely possible, but will take more thought. Where does the
data associated to the name come from? How many names are you talking
about?

A significant problem with a dropdown in a Word formfield is that it is
limited to 25 entries. A better approach might be to use Userform to
fillin your invoices.

Let's say you have 100 clients with the appropriate data (for this
example I am using "Name", "Age", "Address simply because I have
something similar to what you need arlready in progress).

This information is in a Word file C:\DataBase.doc in a 3 column X 101
row tables. Row 1 is the headings, rows 2-101 contain the data.

Now in the invoice template I create a Userform that contains 1
listbox (contains the list of names) and 1 command button.

The UserForm is named UF4

The template has bookmarks "Name" "Age" "Address" inserted where the
data needs to appear

The invoice template has code similiar to:

Sub Auto_New()
Dim myFrm As UF4
Set myFrm = New UF4
myFrm.Show
Unload myFrm
Set myFrm = Nothing
End Sub

The UF code is as follows:

Private Sub UserForm_Initialize()
Dim oDataSource As Word.Document
Dim i As Long
Set oDataSource = Documents.Open("C:\DataBase.doc", Visible:=False)
For i = 2 To oDataSource.Tables(1).Rows.Count
Me.ListBox1.AddItem Left(oDataSource.Tables(1).Cell(i, 1).Range.Text,
_
Len(oDataSource.Tables(1).Cell(i, 1).Range.Text) - 2)
Next
oDataSource.Close
End Sub

Private Sub CommandButton1_Click()
Dim oDataSource As Word.Document
Set oDataSource = Documents.Open("C:\DataBase.doc", Visible:=False)
Dim oNameRng As Word.Range
Dim oAgeRng As Word.Range
Dim oAddressRng As Word.Range
Dim oBM As Bookmarks
Dim i As Long
Set oBM = ActiveDocument.Bookmarks
Set oNameRng = oBM("Name2").Range
Set oAgeRng = oBM("Age2").Range
Set oAddressRng = oBM("Address2").Range
i = Me.ListBox1.ListIndex
oNameRng.Text = Me.ListBox1.Value
oBM.Add "Name2", oNameRng
oAgeRng.Text = Left(oDataSource.Tables(1).Cell(i + 2, 2).Range.Text, _
Len(oDataSource.Tables(1).Cell(i, 1).Range.Text) - 2)
oBM.Add "Age2", oAgeRng
oAddressRng.Text = Left(oDataSource.Tables(1).Cell(i + 2,
3).Range.Text, _
Len(oDataSource.Tables(1).Cell(i, 1).Range.Text) - 2)
oBM.Add "Address2", oAddressRng
Documents("C:\DataBase.doc").Close
Me.Hide
End Sub

Note: The above code is just scratched together and would required
work to handle such things blank rows in the datasource, canceling,
etc.

HTH

force530 wrote:
I have a one page invoice type form. What I need is to select the name from a
drop down list and have subsequent fields (address, city, state, zip, SSN)
fillin automatically based on the "name" entry. Preferably not on the same
line or row, but in a downward format. i.e.,

NOT - John Doe, 123 Any St, Anytown, USA

Prefer - John Doe
123 Any St
Anytown, USA