Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-24-2020, 12:19 PM
src144 src144 is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2019
Novice
Drop Down List to Automatically Populate Data in a Table
 
Join Date: Nov 2020
Posts: 4
src144 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 11-24-2020, 01:09 PM
gmaxey gmaxey is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,189
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 11-24-2020, 02:47 PM
macropod's Avatar
macropod macropod is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,731
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Alternatively, without getting into the complexities of CustomXMLParts:
Attached Files
File Type: docm Pre-Populate Table Data.docm (37.3 KB, 9 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 11-30-2020, 12:30 PM
src144 src144 is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2019
Novice
Drop Down List to Automatically Populate Data in a Table
 
Join Date: Nov 2020
Posts: 4
src144 is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 11-30-2020, 06:33 PM
macropod's Avatar
macropod macropod is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,731
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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.
Attached Files
File Type: docm Pre-Populate Table Data.docm (41.7 KB, 4 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 12-01-2020, 07:30 AM
src144 src144 is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2019
Novice
Drop Down List to Automatically Populate Data in a Table
 
Join Date: Nov 2020
Posts: 4
src144 is on a distinguished road
Default

How would I go about adding additional code to make these specific content controls uneditable?
Reply With Quote
  #7  
Old 12-01-2020, 07:37 AM
gmaxey gmaxey is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,189
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #8  
Old 12-03-2020, 01:52 PM
src144 src144 is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2019
Novice
Drop Down List to Automatically Populate Data in a Table
 
Join Date: Nov 2020
Posts: 4
src144 is on a distinguished road
Default

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
Attached Files
File Type: docm Test Contract.docm (35.6 KB, 0 views)

Last edited by macropod; 12-03-2020 at 05:36 PM. Reason: Added code tags
Reply With Quote
  #9  
Old 12-03-2020, 04:15 PM
macropod's Avatar
macropod macropod is offline Drop Down List to Automatically Populate Data in a Table Windows 10 Drop Down List to Automatically Populate Data in a Table Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,731
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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.
Attached Files
File Type: docm Pre-Populate Table Data.docm (42.0 KB, 3 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

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
Drop Down List to Automatically Populate Data in a Table 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft