#1
|
|||
|
|||
Drop Down List to Automatically Populate Data in a Table
Hello -
I am trying to create a fillable form for a company contract. I would like to be able to choose a drop for Total Hours and then automatically populate the following table with the data listed at the bottom of the document. Is this possible? I am not well versed in Macros/VBA. Help is GREATLY APPRECIATED! Attached document - Pre-Populate Table Data.docx |
#2
|
|||
|
|||
See attached files.
What you see is sort of a super-charged technique using a mapped content control titled "Total Hours," a CustomXMLPart event monitor, a target rich text CC and three building blocks stored in the template named "_1040, _780 and _520" People do this sort of thing for a living |
#3
|
||||
|
||||
Alternatively, without getting into the complexities of CustomXMLParts:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Thank you so much macropod! I should have mentioned, this will be a restricted document. It won't work after I restrict it for fillable forms. Is there a way around this?
|
#5
|
||||
|
||||
You could leave the table unprotected, or add a content control to each cell so that the macro can write to those. The attachment below takes the second approach. Additional code could be added to make the content controls in the table uneditable by the user.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
How would I go about adding additional code to make these specific content controls uneditable?
|
#7
|
|||
|
|||
Code:
For j = 2 To 7 With .Cell(i, j).Range.ContentControls(1) .LockContents = False .Range.Text = Split(StrTmp, ",")(j - 2) .LockContents = True End With Next |
#8
|
|||
|
|||
So this is what I have...This contract will contain other content controls and dropdowns. This whole document needs to be restricted to fillable forms and having these content controls uneditable (as other areas do need to be edited).
Code:
Option Explicit Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) Application.ScreenUpdating = False Dim i As Long, j As Long, StrDetails As String, StrTmp As String With ContentControl If .Title = "Hours" Then For i = 1 To .DropdownListEntries.Count If .DropdownListEntries(i).Text = .Range.Text Then StrDetails = .DropdownListEntries(i).Value Exit For End If Next With ActiveDocument.Tables(2) For i = 1 To 2 StrTmp = Split(StrDetails, "|")(i - 1) For j = 2 To 7 .Cell(i, j).Range.Text = Split(StrTmp, ",")(j - 2) Next Next End With End If End With Application.ScreenUpdating = True End Sub Where would I enter?... Code:
For j = 2 To 7 With .Cell(i, j).Range.ContentControls(1) .LockContents = False .Range.Text = Split(StrTmp, ",")(j - 2) .LockContents = True End With Next Last edited by macropod; 12-03-2020 at 05:36 PM. Reason: Added code tags |
#9
|
||||
|
||||
Replace:
Code:
For j = 2 To 7 .Cell(i, j).Range.Text = Split(StrTmp, ",")(j - 2) Next Code:
For j = 2 To 7 With .Cell(i, j).Range.ContentControls(1) .LockContents = False .Range.Text = Split(StrTmp, ",")(j - 2) .LockContents = True End With Next
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto populate form (data from excel) in Word based on drop down list selection (data from excel) | wvlls | Word VBA | 1 | 03-22-2019 02:29 PM |
Populate grocery list from drop down menu | jlshanke | Excel | 3 | 08-21-2018 07:28 AM |
How to add automatically another drop-down list after insertion of data | doomie83 | Word VBA | 1 | 04-19-2016 04:28 PM |
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 |
Populate dropdown list with data from Access table | spw4000 | Office | 0 | 02-24-2012 05:22 AM |