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
5
192 Views
Last Modified: 2014-09-17
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
Comment
Question by:sherman6789
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 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

'PARAMETER: Login Name for user

'RETURNS: String with selected information about
'user, or empty string if there is no such
'login on the current domain

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

'EXAMPLE: msgbox UserInfo("Administrator")

Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim oRoot As IADs
Dim oDomain As IADs
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

Dim user As IADsUser

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=" _
  & LoginName & "))"
sAttribs = "adsPath"
sDepth = "subTree"

sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
                   
conn.Open _
  "Data Source=Active Directory Provider;Provider=ADsDSOObject"
  
Set rs = conn.Execute(sQuery)

If Not rs.EOF Then
    Set user = GetObject(rs("adsPath"))
    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 & "Email Address: " & .EmailAddress & vbCrLf
    sAns = sAns & "Web Page: " & .HomePage & vbCrLf
    sAns = sAns & "Last Login: " & .LastLogin & 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: " _
      & .PasswordExpirationDate
       
    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

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
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

by:sherman6789
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 76

Accepted Solution

by:
GrahamSkan earned 250 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
        cc.DropdownListEntries.Add GetCellText(tbl.Cell(r, 1))
    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

Open in new window

0
 

Author Closing Comment

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

-sherman6789
0

Featured Post

RoboForm Secure Password Management System

RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!

Question has a verified solution.

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

Suggested Solutions

Outlook Free & Paid Tools
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question