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
183 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
  • 2
  • 2
5 Comments
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you.  Your information is very helpful and we will be able to use it on many projects.

-sherman6789
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now