Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
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
Medium Priority
439 Views
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
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
• 12
• 8

LVL 76

Expert Comment

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

Author Comment

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

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

LVL 76

Expert Comment

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

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

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

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

ID: 39804090

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.

Thanks again.

-sherman6789
0

LVL 76

Accepted Solution

GrahamSkan earned 2000 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

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) = ""
r = r + 1
Loop

End Sub

Dropdown.dotx
Staff.xlsx
0

Author Comment

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

LVL 76

Expert Comment

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

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.

Thank you.

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

Author Comment

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

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

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

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

0

Author Comment

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

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

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

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

## Featured Post

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …