[force530]

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

[Greg Maxey]

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

[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

[Greg Maxey]

Well the method that I showed you should work.

If you must use a protected form type of document with fields "and" you
are sure that 25 will be the maximum number of names, then with some
considerable effort ou could include all of the associated data in a
macro to populate other fields and run the macro on exit from the name
field.

Sub RunOnExitNameField
Dim oFF As FormFields
Set oFF = ActiveDocument.FormFields
Select Case oFF("Name").Result
Case Is = "John Miller"
oFF("Street").Result = "123 Cherry Street"
oFF("Town").Result = "Anytown USA"
Case Is = "Mary Jones"
'Etc.
Case Else
'Do Nothing
End Select
End Sub

force530 wrote:
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