Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.pagelayout
force530 force530 is offline
external usenet poster
 
Posts: 2
Default Forms in word 2002

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

  #2   Report Post  
Posted to microsoft.public.word.pagelayout
Greg Maxey Greg Maxey is offline
external usenet poster
 
Posts: 171
Default Forms in word 2002

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


  #3   Report Post  
Posted to microsoft.public.word.pagelayout
force530 force530 is offline
external usenet poster
 
Posts: 2
Default Forms in word 2002

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



  #4   Report Post  
Posted to microsoft.public.word.pagelayout
Greg Maxey Greg Maxey is offline
external usenet poster
 
Posts: 171
Default Forms in word 2002

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




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting WordPerfect 12 files to Word 2003 Curious New Users 4 May 19th 23 02:48 PM
Word & WordPerfect MrsMac Microsoft Word Help 5 June 10th 06 03:14 AM
WP merge file to Word sstires Tables 4 February 14th 06 07:26 PM
I can't find a free, word 2002 download Gradius16493 New Users 16 November 12th 05 08:13 AM
Underscore (_) will not always display in RTF files (Word 2002). David A Edge Microsoft Word Help 6 June 14th 05 10:39 AM


All times are GMT +1. The time now is 12:33 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"