Drop Down List to Automatically Populate Data in a Table

src144 · #1 11-24-2020, 12:19 PM

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

gmaxey · #2 11-24-2020, 01:09 PM

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

macropod · #3 11-24-2020, 02:47 PM

Alternatively, without getting into the complexities of CustomXMLParts:

src144 · #4 11-30-2020, 12:30 PM

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?

macropod · #5 11-30-2020, 06:33 PM

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.

src144 · #6 12-01-2020, 07:30 AM

How would I go about adding additional code to make these specific content controls uneditable?

gmaxey · #7 12-01-2020, 07:37 AM

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

src144 · #8 12-03-2020, 01:52 PM

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

macropod · #9 12-03-2020, 04:15 PM

Replace:

Code:

        For j = 2 To 7
          .Cell(i, j).Range.Text = Split(StrTmp, ",")(j - 2)
        Next

with

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

See attached.

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