Solved

On a Word Template, can we use a pulldown to make a selection and all associate fields will automatically populate?

Posted on 2014-01-23
20
415 Views
Last Modified: 2014-02-25
Is the following possible using Microsoft Word version 10?  We have created a Word Template.  One of the fillable fields is a pull down with all possible requester's names.  When the user select a requeter from the list, we would like all of the requester's associated data to automatically populate certain fields.  EXAMPLE:  The user selects SHELBY from the requester list and other fields such as her PHONE NUMBER, OFFICE LOCATION, TITLE, EMAIL ADDRESS will immediately and automatically be populated in selected fields.  (ALSO, IF POSSIBLE) If the user changes the name of the Requester, the other associated fields will be automatically updated with information for that requester.

Thanks for any assistance that you can give.

sherman6789
0
Comment
Question by:sherman6789
  • 12
  • 8
20 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39803694
Do you have Word version 2002, internally numbered version 10, or Word 2010, internal version 14?
0
 

Author Comment

by:sherman6789
ID: 39803714
I clicked on the Help button and got the following information:

Microsoft Professional Plus 2010
version 14.0.7106.5003 (32-bit)
Product ID 02260-018-0000106-48794

I hope that this helps.  If it does not, I will contact Microsoft and ask them your question.  I have not been able to find anyone who knows anything about "Internally number".

Thanks.

sherman6789
0
 

Author Comment

by:sherman6789
ID: 39803758
I now see that it is 2010 version 14.  Thanks. -sherman6789
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39803773
Yes, it helps. Version 2010 (internal 14) means that you are probably using a Content Control from the Controls group on the Developer tab.

You would have to use some VBA. Where would the other details come from?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39803805
You seem to have just posted essentially the same question. That is against EE's policy since it opens the possibility of exceeding the points limit per question.

Can I suggest that you delete it before it receives any comments, after which you won't be able to do so?
0
 

Author Comment

by:sherman6789
ID: 39803812
Thanks for your swift response GrahamSkan.  The detail could come a list or table in Word, Excel Spreadsheet, Access database, or any other list that you recommend.  We have a list that is on a shared directory and is updated frequently by the office secretarial staff.  That list is in Excel and has several fields such as phone numbers, email addreses, room number, etc.  This could be used.  Otherwise, I will need to create a special list or table.  Since employees that may not be familiar with modifying a pulldown list, I think that it would be better to use a separate table or list.  The form being used is a Template and has been locked against changes.

sherman6789
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39803847
The list could stay in Excel. It would be possible to load the pulldown with the names found in the list, and to address the list again when the selection is made.

I'll try to come up with some demo code.
0
 

Author Comment

by:sherman6789
ID: 39804090
Thanks GrahamSkan.  I made a mistake.  I had intended to ask two separate questions.

1.  How to create a pulldown where the information for the field would come from a table or list rather than come from the pulldown setup itself.

2.  How to get the MS Word program to pull several fields of information and place the answers into separate associated fields after the user has selected a choice from a pulldown button that uses an outside table or list.

You are correct, I made the mistake of asking the second question before I got a working answer to the first question and they are associated.

I was told a long time ago that Expert-Exchange does not like users to ask several questions in the same thread because the experts would be cheated out of points.  I was not trying to do anything wrong and I wanted the experts to get as many points as possible for their hard work in trying to answer questions from people like me.

I just took your advice and deleted the other thread before anyone had a chance to answer the question.

Thanks again.

-sherman6789
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39804648
Examples of a Word template and an Excel Workbook are attached.
The code needs to go in the template's ThisDocument module
Option Explicit

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWks As Excel.Worksheet

Function GetSheet() As Excel.Worksheet
    On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    
    xlApp.Visible = True
    Set xlWbk = xlApp.Workbooks.Open("I:\Allwork\ee\28346442\Staff.xlsx")
    Set GetSheet = xlWbk.Sheets(1)

End Function

Private Sub Document_ContentControlBeforeContentUpdate(ByVal ContentControl As ContentControl, Content As String)
Stop
End Sub

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim r As Integer
If ContentControl.Title = "Name" Then
    If xlWks Is Nothing Then
        Set xlWks = GetSheet
    End If
    r = 2
    Do Until xlWks.Cells(r, 1) = ""
        If xlWks.Cells(r, 1) = ContentControl.Range.Text Then
            ActiveDocument.SelectContentControlsByTitle("Phone")(1).Range.Text = xlWks.Cells(r, 2)
            ActiveDocument.SelectContentControlsByTitle("Office")(1).Range.Text = xlWks.Cells(r, 3)
            Exit Sub
        End If
        r = r + 1
    Loop
    
    MsgBox ContentControl.Range.Text & " not found in source worksheet"
End If
End Sub


Private Sub Document_New()
    Dim cl As Word.Cell
    Dim tbl As Word.Table
    Dim r As Long
    Dim doc As Document
    Dim strText As String
    Dim cc As ContentControl
    
    Set xlWks = GetSheet
    Set doc = ActiveDocument
    Set cc = doc.SelectContentControlsByTitle("Name")(1)
    cc.DropdownListEntries.Clear
    r = 2
    Do Until xlWks.Cells(r, 1) = ""
        cc.DropdownListEntries.Add xlWks.Cells(r, 1), xlWks.Cells(r, 1)
        r = r + 1
    Loop

End Sub

Open in new window

Dropdown.dotx
Staff.xlsx
0
 

Author Comment

by:sherman6789
ID: 39815225
To:  GrahamSkan.  Thank you for your codes.  My job has temporarily placed me on another assignment.  I will continue with this project very soon.

When I begin again, I think that I have to place VBA code using the Alt-9 or Alt-F9 to enter the code into the system.  Is that correct?  The code that you wrote should be placed in the Staff.xlsx workbook sample and the Dropdown.dotx will also work with the code.

I am going to read some more to learn how to add the files together and make everything work.

I will continue very soon.   Thanks again.

sherman6789
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39815948
The code needs to go in the ThisDocument module of the Word template. Alt+F11 will open the VBA editor. (Keystroke short-cuts which include F9 deal with Word fields.)

Don't forget to change line 16 to point to the workbook on your system.
0
 

Author Comment

by:sherman6789
ID: 39819309
Hello again GrahamSkan,  I am back in town but need a few more days before I can work on the project again.  Thanks for the information about Alt+F11 and the information about Line 16.

I have attached two files that are similar the the real one that I am working with.  Names and numbers have been changed to protest privacy.  This may give you more of an idea of what I am trying to do.  So far, everything is working and I am about ready to see if a Requester name can be selected and the Phone number and Email address can be automatically input in the correct fields.

I did take your advice about removing the second thread.  See my message to you above your vba code.

Thank you.

sherman6789
EE-WORKPLAN-STAFF-TEST.xlsx
EE--WORKPLAN-TEMPLATE-TEST.dotx
0
 

Author Comment

by:sherman6789
ID: 39830915
Good evening GrahamSkan,

I copied your "Staff" and "Dropdown" files in a directory called EE-Test.  I then highlighted your VBA code.  I changed line 16 to: Set xlWbk = xlApp.Workbooks.Open("h:\sherman\EE-Test\staff.xlsx").  Next, I opened the Staff spreadsheet and clicked Alt-F11.  A dark screen came up but I was not able to paste your code at that point. I clicked "View" and "Code" (F7)and a white screen came up.  I then pasted you code on that screen.  It would not let me save and it posted the following message: "VB Project can not be saved in macro-free workbook."  If you have time, please give me the steps to follow.  I know that you are working with many other people and I don't expect you to spend too much of your time helping me.  Most of the things that I need to have done have been accomplished.  If I am not able to do this last part soon.  I will close the question so that you can receive your points and I will continue to study the information on Word, Access and Excel.  Thank you for your kind assistance.
-sherman6789
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39834462
The code is in the Word template and doesn't need to be put in the WorkBook.

The dropdown content control list is filled for a new document created from the template. You can either double click on the template name in Windows Explorer or you can use File/New in Word. If the template isn't in the recently used list, try 'New from existing...'

Then you should be able to select a name in the dropdown. When the focus leaves the control, the other two fields should be filled in
0
 

Author Comment

by:sherman6789
ID: 39847402
Thank you GrahamSkan.  I am back again.  I plan to work on this project today and will let you know how it works.  Your instructions seem straight forward and I should be able to follow them.  Thanks.  This thread will end soon.

sherman6789
0
 

Author Comment

by:sherman6789
ID: 39847994
Hello again GrahamSkan,

This is what I did today.  I saved your two files: DROPDOWN (MS Word) and STAFF (Excel) onto a thumb drive.

I opened the Word document DropDown and clicked Alt-F11.  I clicked "TemplateProject (Dropdown); then Micro Word Object; then "ThisDocument".  (I hope that this is the correct way to get to "ThisDocument".)

I pasted your VBA code on the page and updated the location of the Excel file as: (F:\staff.xlsx").  

I clicked the DEVELOPER button.  Then Restricted Editing, Allow only this type... "Filling in Forms".

I clicked Yes, Start Enforcing Protection, left password blank for this test
I tried to save to document and a message popped up stating that I needed to change the settings to Macro Enabled.  I changed the setting from "Word Template" to "Word Macro-Enabled Template".

Nothing happened when I selected the pulldown for the name.

What did I miss?

Thanks.

sherman6789

P.S.: please check out this article: http://word.mvps.org/faqs/macrosvba/documentevents.htm
0
 

Author Comment

by:sherman6789
ID: 39848058
After experimenting some more with the project, I received the following error message:

++++++++++++++++++++++++++++++++++++
COMPILE ERROR:
User-defined type not defined.
++++++++++++++++++++++++++++++++++++

The following line was highlighted in red type:
Dim xlApp As Excel.Application

Thanks,

sherman6789
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39848746
You need to set a reference to the Microsoft Excel Object Library in the Word template. This is done via Tools/References in the VBA editor.
0
 

Author Comment

by:sherman6789
ID: 39868893
Hello,

I am in the process of trying to set a reference to the Microsoft Excel Object Library in the Word Template.  I am attempting to use the tools/Reference in the editor.  Having some trouble and hope to get it done very soon.  Sorry for the delay but I will continue to try.  Thanks for your assistance and patience.

Thanks.
0
 

Author Closing Comment

by:sherman6789
ID: 39886101
Thanks to  GrahamSkan for the expert assistance given.
0

Featured Post

A Knowledge Base That Stays Up-to-Date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

16 Experts available now in Live!

Get 1:1 Help Now