Solved

# There are some dropdowns on our form. We would like some of the other fields to be automatically updated when certain data is selected with a drop-down.

Posted on 2014-09-05
Medium Priority
200 Views
We have a MS Word Template created where the users must fill-in blanks.  Some of the blanks are "Pull-downs".  When certain pull-downs are selected, we would like several of the other blanks to be filled-in automatically.

Example:  When the requester selects his or her name from name pull-down, we would like the requester's "phone number", "Job Title" and "address" to be automatically input into the proper cells.  If the requester's name is later changed, we would like the automatic fill-ins to be automatically updated also,

If this is a very involved procedure, we thinks that it might not be worth doing.

Any assistance or suggestions that you can give will be appreciated.

Thanks,

sherman6789
0
Question by:sherman6789
• 2
• 2

LVL 41

Assisted Solution

Kyle Abrahams earned 1000 total points
ID: 40306606
You would need some VBA to do it.

Essentially you would create a macro that looks like

sub myDropDown_Change ()

end sub

that would fire so the routine every time someone selects the dropdown.  The next question is where are you going to store the information.  (EG:  Person A . . . where is person A's info for job title and what not?)

If you're going from active directory you can use the following from http://www.freevbcode.com/ShowCode.asp?ID=710

Public Function UserInfo(LoginName As String) As String
'PURPOSE: Display information that is available in
'the Active Directory about a given user

'RETURNS: String with selected information about
'user, or empty string if there is no such

'REQUIRES: Windows 2000 ADSI, LDAP Provider
'Proper Security Credentials.

Dim sBase As String
Dim sFilter As String
Dim sDomain As String

Dim sAttribs As String
Dim sDepth As String
Dim sQuery As String
Dim sAns As String

On Error GoTo ErrHandler:

'Get user Using LDAP/ADO.  There is an easier way
'to bind to a user object using the WinNT provider,
'but this way is a better for educational purposes
Set oRoot = GetObject("LDAP://rootDSE")
'work in the default domain
sDomain = oRoot.Get("defaultNamingContext")
Set oDomain = GetObject("LDAP://" & sDomain)
sBase = "<" & oDomain.ADsPath & ">"
'Only get user name requested
sFilter = "(&(objectCategory=person)(objectClass=user)(name=" _
sDepth = "subTree"

sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth

conn.Open _

Set rs = conn.Execute(sQuery)

If Not rs.EOF Then
With user

'if the attribute is not stored in AD,
'an error will occur.  Therefore, this
'will return data only from populated attributes
On Error Resume Next

sAns = "First Name: " & .FirstName & vbCrLf
sAns = sAns & "Last Name " & .LastName & vbCrLf
sAns = sAns & "Employee ID: " & .EmployeeID & vbCrLf
sAns = sAns & "Title: " & .Title & vbCrLf
sAns = sAns & "Division: " & .Division & vbCrLf
sAns = sAns & "Department: " & .Department & vbCrLf
sAns = sAns & "Manager: " & .Manager & vbCrLf

sAns = sAns & "Phone Number: " & .TelephoneNumber & vbCrLf
sAns = sAns & "Fax Number: " & .FaxNumber & vbCrLf

sAns = sAns & "Web Page: " & .HomePage & vbCrLf
sAns = sAns & "Last Logoff: " & .LastLogoff & vbCrLf

sAns = sAns & "Account Expiration Date: " _
& .AccountExpirationDate & vbCrLf

'IN RC2, this returned 1/1/1970 when password
'never expires option is set
sAns = sAns & "Password Expiration Date: " _

End With
End If
UserInfo = sAns
ErrHandler:

On Error Resume Next
If Not rs Is Nothing Then
If rs.State <> 0 Then rs.Close
Set rs = Nothing
End If

If Not conn Is Nothing Then
If conn.State <> 0 Then conn.Close
Set conn = Nothing
End If

Set oRoot = Nothing
Set oDomain = Nothing
End Function

0

LVL 77

Expert Comment

ID: 40306620
This can be done using VBA.

A couple of clarifications are necessary.

Firstly there are three types of dropdown that can be put on Word documents - legacy form fields, Active X  and Content controls. They all require different techniques.

Secondly, where would the extra data come from?
0

Author Comment

ID: 40313043
Thanks to Kyle Abrahams & GrahamSkan for responding to my question.

We have developed an activity request form that is located on a shared directory in the office.  It was created in MS Word as a template.  Users will open the template and answer the questions shown.  They will then save the file under a new name and submit the printed copy to the department for action.

The form contains several drop-down items to help save time when the user is completing the request form.  A modified sample form has been attached to this request for the experts to evaluate.

There is one areas where the user selects a Requester's name from the drop-down.  If possible, we would like the selected "Requester" name to cause two other areas to be automatically completed.  For example:  If the user select a Requester's name from the drop-down the requester's phone number and email address will automatically be populated in the correct fields.  If the user later changes the "Requester's" name from the drop-down, the new Requester's phone number and email address will automatically be updated with the correct information.

I am guessing that there should be a table saved in the directory that contains all of the requester's names, phone numbers and email addresses.  THis table can be a Word table, database or Excel spreadsheet.  When a requester's name is selected, the other information will also be selected and entered into the correct boxes.  I have created a small table for this purpose.  I don't know how to use it in relations to drop-down boxes.  This is where the "extra" data will com from.  Of the three types of drop-downs that you mentioned, which one should I use.  Also, check the attachment and let me know which type did I use.

Any assistance that you can give will be appreciated.

Thank you.

sherman6789
F--WORK-PLAN-EE-Sample-Only-9-9-14.docx
F--SAMPLE-EE-TABLE-INFO-FOR-DROP-DOWN.do
0

LVL 77

Accepted Solution

GrahamSkan earned 1000 total points
ID: 40313511
You are using content controls. They are the ideal choice if you don't have to support Word 2003 or earlier.

I converted your form document into a template and put this code in the ThisDocument module.
There is a macro to fill the Requester content control at the time that a new document is created (File/New) from the template. This should be easier to maintain than filling the control manually.
There is also a macro to fill the email and phone controls whenever the selection leaves the Requester control. I have given them titles, but you might prefer to use tags.

I have hard-coded the path to the word document with the table, so you will have to adjust that. I think that it would be better to use a database or Excel. You can use ADO to extract the data without opening a document. It will look much cleaner to the user.
Option Explicit

Dim docTable As Document
Dim strDocTablePath As String
Dim strDocTableName As String
Dim tbl As Table
Dim docForm As Document

Sub Document_New()
Dim cc As ContentControl
Dim r As Integer

Set docForm = ActiveDocument
strDocTableName = "F--SAMPLE-EE-TABLE-INFO-FOR-DROP-DOWN.docx"
strDocTablePath = "C:\Users\Graham Skan\Documents\Allwork\ee\28512669"
Set docTable = Documents.Open(strDocTablePath & "\" & strDocTableName)
Set tbl = docTable.Tables(1)

Set cc = docForm.SelectContentControlsByTitle("REQUESTER")(1)
cc.DropdownListEntries.Clear
For r = 2 To tbl.Rows.Count
Next r
docTable.Close
End Sub

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim ccPhone As ContentControl
Dim ccEmail As ContentControl
Dim r As Integer

If ContentControl.Title = "REQUESTER" Then
Set docTable = Documents.Open(strDocTablePath & "\" & strDocTableName)
Set tbl = docTable.Tables(1)

Set ContentControl = docForm.SelectContentControlsByTitle("REQUESTER")(1)
For r = 2 To tbl.Rows.Count
If ContentControl.Range.Text = GetCellText(tbl.Cell(r, 1)) Then
Set ccPhone = docForm.SelectContentControlsByTitle("PHONE")(1)
ccPhone.Range.Text = GetCellText(tbl.Cell(r, 2))
Set ccEmail = docForm.SelectContentControlsByTitle("EMAIL")(1)
ccEmail.Range.Text = GetCellText(tbl.Cell(r, 3))
Exit For
End If
Next r
docTable.Close
End If
End Sub

Function GetCellText(cl As Word.Cell) As String
Dim rng As Word.Range

Set rng = cl.Range
rng.MoveEnd wdCharacter, -1 'drop cell formatting characters
GetCellText = rng.Text
End Function

0

Author Closing Comment

ID: 40329196
Thank you.  Your information is very helpful and we will be able to use it on many projects.

-sherman6789
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll