#1
|
|||
|
|||
Word Template with a drop down list to populate other fields
Hi everyone,
Your help is truly needed. I have provided a sample Word template attached of what I need to be better explain it. I have a project that I have to complete a drop down list with over 400 entries to choose from. I want to be able to choose a provider from the drop down list and it populate the provider's address, clients name, clients date of birth, clients address, clients prefix, and group number. The thing is I have over 400 different clients. How can this be done? Thanks in advance. |
#2
|
|||
|
|||
You are going have to roll up your sleeves and write an awful lot of code or tie your content control dropdown list to a data base.
I really don't see how "Clients Name" is associated with a selected provider but if you have a Excel files "Providers.xlsx" in the same folder as your template then something like this: Code:
Option Explicit Dim arrData As Variant Private Sub Document_ContentControlOnEnter(ByVal oCC As ContentControl) Dim lngIndex As Long Dim strSQL As String Select Case oCC.Title Case "Provider" oCC.DropdownListEntries.Clear strSQL = "SELECT * FROM [Sheet1$];" xlFillList arrData, ThisDocument.Path & "\Providers.xlsx", "True", strSQL For lngIndex = 0 To UBound(arrData, 2) oCC.DropdownListEntries.Add arrData(0, lngIndex), arrData(0, lngIndex) Next lngIndex End Select End Sub Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean) Dim lngIndex As Long Select Case oCC.Title Case "Provider" If Not oCC.ShowingPlaceholderText Then ActiveDocument.SelectContentControlsByTitle("Provider Address").Item(1).Range.Text = arrData(1, fcnDropDownIndex(oCC)) ActiveDocument.SelectContentControlsByTitle("Client Name").Item(1).Range.Text = arrData(2, fcnDropDownIndex(oCC)) Else ActiveDocument.SelectContentControlsByTitle("Provider Address").Item(1).Range.Text = vbNullString ActiveDocument.SelectContentControlsByTitle("Client Name").Item(1).Range.Text = vbNullString End If End Select End Sub Function fcnDropDownIndex(CC As ContentControl) As Long Dim lngIndex As Long For lngIndex = 1 To CC.DropdownListEntries.Count If CC.DropdownListEntries.Item(lngIndex).Text = CC.Range.Text Then fcnDropDownIndex = lngIndex - 1 Exit For End If Next lbl_Exit: Exit Function End Function Public Function xlFillList(arrPassed As Variant, strWorkbook As String, _ bSuppressHeader As Boolean, strSQL As String) Dim oConn As Object Dim oRS As Object Dim lngNumRecs As Long Dim strConnection As String 'Create connection: Set oConn = CreateObject("ADODB.Connection") If bSuppressHeader Then strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Else strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=NO"";" End If oConn.Open ConnectionString:=strConnection Set oRS = CreateObject("ADODB.Recordset") 'Read the data from the worksheet. oRS.Open strSQL, oConn, 3, 1 '3: adOpenStatic, 1: adLockReadOnly With oRS 'Find the last record. .MoveLast 'Get count. lngNumRecs = .RecordCount 'Return to the start. .MoveFirst End With arrPassed = oRS.GetRows(lngNumRecs) 'Cleanup If oRS.State = 1 Then oRS.Close Set oRS = Nothing If oConn.State = 1 Then oConn.Close Set oConn = Nothing lbl_Exit: Exit Function End Function |
#3
|
|||
|
|||
@gmaxey Word Template with a drop down list to populate other fields
@gmaxey thanks for your response. I do have an Excel file that I will be using as the source file. I have attached a sample of that file to this post and it is what I had initially planned to use to execute this project. Will this work?
|
#4
|
|||
|
|||
What is the relationship between "Provider" and the client. If you Choose "Dell" from your list, what client to you want associated with it?
|
#5
|
|||
|
|||
@gmaxey It's supposed to be the other way around if I choose the Client Name then I would like for the Provider (Dell, HP, or Microsoft) info along with clients info to populate in the appropriate fields. For example, in the template is states "Guidelines for submitting information for clients from Choose an item." Therefore, if I choose the Client Name Adair County (MO) from the drop down then I would like for Dell (Provider), Provider address, Client Prefix, Client Group, and the Client Addresses to populate once I choose the Client Name from the drop down list.
|
#6
|
||||
|
||||
I am as confused as my old friend Greg. Your workbook has client names and data associated with the provider, but your document is client oriented and the information relating to the client is not present in the workbook, and the fields relating to the provider are not present in the document.
It is easy enough to grab the information associated with a particular record and apply it to the document, but if you are going to do it with content controls, then you need to have the content controls titled and/or tagged and the information you want in them must be available from the worksheet record.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
@gmaxey @gmayor Word Template with a drop down list to populate other fields
Correction: I have added the correct/revised Word Template of what I am needing help with. I have highlighted in yellow the entry that I would like to have a drop down list for and once that entry is selected from the drop down the other fields will populate (I have bolded as shown below). This isn't the actual information but I just wanted to give a general idea of what the format of the template will be. I will be using over 400 client names to choose from along with its corresponding provider & client info and I am not sure if it would be best to use VBA and have the excel file as my source. Please help. Thanks again for responding.
- Guidelines for submitting information for your client Adair County (MO) and Provider Dell. · Please complete applicable fields on the claim · All information should be submitted for payment to the provider address: P.O. BOX 1234 MISSOULA, MONTANA 59806-1234 · Item Name: Click here to enter text. Item Control#: Click here to enter text. · Client Address: 123 Bradshaw St. Louis, MO 12345 · Client Prefix: ARK · Client Group Number: 790123 |
#8
|
|||
|
|||
You have already been given the solution to this requirement practically on a silver platter. If you are then going to move the goal post after the start of the game at least take the time to read and try to apply it.
Basically you need to change your OnEntry procedure as such: Private Sub Document_ContentControlOnEnter(ByVal oCC As ContentControl) Dim lngIndex As Long Dim strSQL As String Select Case oCC.Title Case "Client" oCC.DropdownListEntries.Clear strSQL = "SELECT * FROM [Sheet1$];" xlFillList arrData, ThisDocument.Path & "\Providers.xlsx", "True", strSQL For lngIndex = 0 To UBound(arrData, 2) 'Since you have a gazillion empty records in your excel file: If Not IsNull(arrData(0, lngIndex) Then oCC.DropdownListEntries.Add arrData(0, lngIndex), arrData(0, lngIndex) End If Next lngIndex End Select End Sub The rest of the code would be edited accordingly (e.g., the OnExit) and all the rest of the CCs should be text CCs. |
#9
|
|||
|
|||
Got it! Thanks a bunch! @gmaxey
|
#10
|
|||
|
|||
Sorry to bother you but I have tried running the code that you have provided but I keep getting the macros popup box to create a macro. I have already entered my Content Controls and I am not sure what it is that I am missing? I apologize but I have basic VBA knowledge and don't intend to make this harder than what it really is.
|
#11
|
|||
|
|||
Is the code you are showing in the ThisDocument module of the document or template?
What do you mean by '... but I keep getting the Create Macros popup menu?" Keep getting it when? What exactly are you doing? |
#12
|
|||
|
|||
Word Template with a drop down list to populate other fields
The code is in the "ThisDocument" module of the normal document. Once I press F5 to run the code the shown dialog box pops up.
|
#13
|
|||
|
|||
Put it in the this document module of the active document. You don't run any macro. The ContenControl OnEnter event fires when you enter the CC.
|
#14
|
|||
|
|||
Word Template with a drop down list to populate other fields
Greg,
I have tried doing as you suggested however I get the error message "data source name is too long. It seems as if this is the only issue. Please see the screenshots. |
#15
|
|||
|
|||
Have you searched for information on that error? Maybe you could use a shorter data source name.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA coding for multiple drop-down lists to populate a text box for each list | yeatropulo | Word VBA | 14 | 11-11-2015 01:08 PM |
Error 5941 when running my macro to auto populate fields throughout the word doc | VBAnovice1 | Word VBA | 2 | 05-26-2015 01:35 AM |
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data | Faldinio | Word VBA | 7 | 10-19-2014 06:03 AM |
how do I auto-populate a word template | Greenhorn | Word | 4 | 09-05-2012 04:16 PM |
MSWord forms use list to populate successive text fields | 2surgeons | Word | 4 | 03-05-2012 07:19 PM |