autofill of a textbox from a listbox selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott Noles - 22 Nov 2006 19:44 GMT

Hello All,

I am new to this group and I do not see my question on the list of items. I
am currently working on creating a Word Template to be used by an
administrative assistant to help complete meeting communication. This
template has a listbox that includes the location of 4 meeting sites. I
would like the administrative assistant to be able to select one of the
sites and have the macro fill a textbox with that site's address and phone
number. The macro would run on exit of the listbox. I have not worked with
the VBA editor yet so any advice would be great.

Thank you.

Reply to this Message

Doug Robbins - Word MVP - 22 Nov 2006 20:01 GMT

It is not clear what type of form you are talking about, but I am assuming
that it is one using formfields in a protected document. If that is the
case, if you store the address details for each site as autotext, with the
name of each autotext entry corresponding to the site name and then you have
the sites in a dropdown type formfield, then the following code run on exit
from that formfield will do what you want:

' Macro created 15-11-97 by Doug Robbins to add the address corresponding to
a drop down name

'

Set myDrop = ActiveDocument.FormFields("Dropdown1").DropDown

Site = myDrop.ListEntries(myDrop.Value).Name

Address = ActiveDocument.AttachedTemplate.AutoTextEntries(Site).Value

ActiveDocument.FormFields("Text1").Result = Address

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hello All,
>
[quoted text clipped - 8 lines]
>
> Thank you.

Reply to this Message

Scott Noles - 22 Nov 2006 20:26 GMT

Doug,

Thank you very much for the reply. Yes I am using the formfields in a
protected document. I do have additional questions. Using the code example
where would I place the actual address entry into the code? Also how can I
format it so it looks correct? I am an extreme rookie when it comes to the
code so I am trying to make sure I understand exactly what it means.

> It is not clear what type of form you are talking about, but I am assuming
> that it is one using formfields in a protected document. If that is the
[quoted text clipped - 28 lines]
>>
>> Thank you.

Reply to this Message

Doug Robbins - Word MVP - 23 Nov 2006 22:24 GMT

You need to create an autotext entry that contains the address details for
each site.

Those autotext entries would be named Site1, Site2, Site3, etc and the items
in the list box would correspond to the autotext extries, that is they would
be Site1, Site2, Site3, etc.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Doug,
>
[quoted text clipped - 36 lines]
>>>
>>> Thank you.

Reply to this Message

Scott Noles - 22 Nov 2006 20:34 GMT

To help clarify for everyone so we are all on the same page. This would be a
protected Word document. What I am looking to do is take a ListBox of four
sites and based upon the selection of one of the 4 sites I would like a
textbox to be filled with the address.

Site 1
Site 2
Site 3
Site 4

Site 1 address
123 Any Street
Home Town, State 12345

Site 2 Address
345 My Street
Other town, State 54323

Site 3 address
876 This street
Town, State 34342

Site 4 Address
124 Ave
City, State 12454

Once I select the site from the listbox then the text with the correct
address is inserted. My assumption is the Macro that I am creating would
have to run on exit from the listbox. I hope this clarifies.

Reply to this Message

John Viall - 27 Nov 2006 02:59 GMT

I'm trying to do the same thing, however, I want to make a selection from a
listbox, and have it autopopulate several locations in a form, with different
information.

For instance, I want to select a Company Name, and have it autopopulate at
different locations on the form, the Address, Phone Number, and Contact
Person.

How can I do this?

Thanks, and sorry for hijacking this discussion.

John

> To help clarify for everyone so we are all on the same page. This would be a
> protected Word document. What I am looking to do is take a ListBox of four
[quoted text clipped - 58 lines]
> >>
> >> Thank you.

Reply to this Message

Doug Robbins - Word MVP - 27 Nov 2006 04:34 GMT

Use a variation of this routine that loads a listbox 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 Clients.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.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> I'm trying to do the same thing, however, I want to make a selection from
> a
[quoted text clipped - 84 lines]
>> >>
>> >> Thank you.

Reply to this Message

John Viall - 27 Nov 2006 19:54 GMT

That worked great, now how would I go about using Words built-in Checkbox to
do the following:
When I select chkBox1, and mark the "X", it will automatically go down
further into the document and select chkBox2 and also mark it.

Thanks,
John

> Use a variation of this routine that loads a listbox with client details
> stored in a table in a separate
[quoted text clipped - 149 lines]
> >> >>
> >> >> Thank you.

Reply to this Message

Doug Robbins - Word MVP - 27 Nov 2006 20:43 GMT

What sort of Checkboxes are we talking about? If of the type as used in a
protected document, you could use a macro that runs on Exit from the first
Checkbox to set the value of the second

With ActiveDocument
.FormFields("Check2").CheckBox.Value =
.FormFields("Check1").CheckBox.Value
End With

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> That worked great, now how would I go about using Words built-in Checkbox
> to
[quoted text clipped - 176 lines]
>> >> >>
>> >> >> Thank you.

Reply to this Message