Read value from drop-down list and populate text box

[Marc Mangus]

Hello gang,

I'm an advanced Word user but I'm just starting to look
at VBA to go further. Here's what I want to do: I have a
form field that is a drop-down list of cities. Based on
the user's selection I want to run a macro that populates
a text box with the address for an office in that city.
It can either a) put the whole address in one text box or
b) enter text into pre-defined, individual fields for
address, city, state, zip etc. I'm using Word 2003.
Ideas? I'd also like some recommendations for beginning
VBA. I have programmed in other languages a bit. Thanks.

 

[Doug Robbins - Word MVP]

Hi Marc,

Use the following code in a macro run on exit from the dropdown:

' Macro created 15-11-97 by Doug Robbins to add the address corresponding to
a drop down name
'
Set myDrop = ActiveDocument.FormFields("Dropdown1").DropDown
Company = myDrop.ListEntries(myDrop.Value).Name
Address = ActiveDocument.AttachedTemplate.AutoTextEntries(Company).Value
ActiveDocument.FormFields("Text1").Result = Address

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Marc Mangus]

Thanks Doug, however I don't see how to specify what the
address is for a particular office. The Address= line
specifies "Autotext" but I'm not sure how to create the
autotext address for each of the 12 offices. Thanks!

 

[Doug Robbins - Word MVP]

Hi Marc,

Here are the instructions from the Word Help file:

Create a new entry

Select the text or graphic you want to store as an AutoText entry.

To store paragraph formatting (paragraph formatting: Formatting that
controls the appearance of a paragraph. Examples include indentation,
alignment, line spacing, and pagination.) with the entry, include the
paragraph mark (¶) in the selection.

On the Insert menu, point to AutoText, and then click New.

When Microsoft Word proposes a name for the AutoText entry, accept the name
or type a new one. (Here you will need to type the name of the city.)

If you plan to insert the entry by using AutoComplete, make sure the name
contains at least four characters because Word inserts an entry only after
four characters have been typed.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Marc Mangus]

I know you are just trying to help and don't get paid but
assuming I didn't read the help file isn't really that
helpful. I did. Let me try this from a different angle.
What I don't understand is how a given "key" in this case
the name of an office, associates with a given "result"
in this case the office address. I'm very familiar with
tables and rows and DB programming where you submit the
key and get a row set back. How does this relate to what
you are proposing? Generating autotext is straightforward
but I don't see how choosing "Austin" from the drop down
(for example) will then populate the text box wih the
street address for the Austin office. Make sense? I
realize I'm a newbie to VBA and I'm trying to communicate
as clearly as I can. Thanks for your help!

 

[Doug Robbins - Word MVP]

Hi Marc,

For your example, you would use Austin as the name of the autotext entry and
you would have Austin as one of the items in the dropdown List.

A userform however may be a better approach for you.

See the article "How to create a Userform" at:

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

and this routine loads a listbox on the userform 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.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

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 Suppliers.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

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
I know you are just trying to help and don't get paid but
assuming I didn't read the help file isn't really that
helpful. I did. Let me try this from a different angle.
What I don't understand is how a given "key" in this case
the name of an office, associates with a given "result"
in this case the office address. I'm very familiar with
tables and rows and DB programming where you submit the
key and get a row set back. How does this relate to what
you are proposing? Generating autotext is straightforward
but I don't see how choosing "Austin" from the drop down
(for example) will then populate the text box wih the
street address for the Austin office. Make sense? I
realize I'm a newbie to VBA and I'm trying to communicate
as clearly as I can. Thanks for your help!

 

[Brian]

Another option would be to assign an array containing the addresses
and use the value of the dropdown to select the correct item from the
array. Something like:

Set myDrop = ActiveDocument.FormFields("Dropdown1").DropDown
ActiveDocument.FormFields("StreetAddress").Result=Street(myDrop.Value)


I generaly like storing larger volumes of information in external
files where it's easy to maintain. You could use that approach as
well.

The external file could contain City,Street,Zip, etc. Loop through
the file until you read EOF. If the name in the dropdown matches the
city name that you are reading then assign the appropriate textboxes.
The heart of the code would look like

Set myDrop = ActiveDocument.FormFields("Dropdown1").DropDown
' Open you external file here
' Start looping through the data in the file here
Input #1,City,Street,Zip
If City = myDrop.ListEntries(myDrop.Value).Name Then
ActiveDocument.FormFields("StreetAddress").Result = Street
ActiveDocument.FormFields("CityAddress").Result = City
ActiveDocument.FormFields("ZipAddress").Result = Zip
GoTo CloseHere
End If
' Loop back
CloseHere:
' Close your file


Hope you find some of these helpful.

-Brian