#1
|
|||
|
|||
auto populate multiple text boxes dependent on a drop down
I have been trying to make this work for a few days now to no avail. I am writing a document that has three pieces of information in it that needs to be repeated and or auto-populated based on the first selection (city,state). The other two are contract number and hotel name. Both contract number and hotel are hard set (only one per city) but the city,state could be one of 65 cities. I need to select the city name on the title page and it auto-populate the contract number and hotel name throughout the rest of the document.
I am new to VBA and have tried to do it using this but no success. I have also tried references and everything else I could try after reading forums. |
#2
|
||||
|
||||
Does your form use formfields, ActiveX controls, or content controls?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
It can be any of them. Whichever would work best is fine with me. I don't know enough about them to know if one is better than the other.
|
#4
|
||||
|
||||
For the form-creation basics, see: https://support.office.com/en-us/art...B-AC44937E3A9F
Since you're working with Office 2007, I'd suggest using content controls, as described in that article. Your requirements are far more involved than that basic introduction covers. Assuming you want to choose the State from a dropdown list and, depending on the State selected, choose a city from another dropdown list of the cities for that State, you'll need to implement a dependent dropdown. This requires some VBA programming. For a document with such a setup, for which the core programming has already been done for you, see: https://www.msofficeforums.com/word-...html#post77762 For the contract number and hotel name, you'll need to implement a dependent text content control. This too requires some VBA programming. For a document with such a setup, for which the core programming has already been done for you, see: https://www.msofficeforums.com/word-...html#post46903 From there, it's a fairly straightforward matter to replicate any of these details throughout the document. One method would be to bookmark each of the initial content controls and simply insert cross-references to those bookmarks wherever you need them. The code that handles the content control dependencies can likewise handle the cross-reference updating. Doubtless you'll have more questions. Feel free to post back.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
it worked
OK so i used the second link you sent with the tags and values set as the contract number. It works beautifully. Now I am trying to reference the information throughout the document. I started by bookmarking the dropdown content control and cross referencing it on the next page. All it seems to have done was copy iy. What I mean is that there is another copy of the dropdown content control but the user would have to manually select the city here also instead of you changing it on the first page and it automatically populating the selected city name elsewhere.
Thoughts? |
#6
|
||||
|
||||
Using the document in the second link as an example, what I'd suggest for the replication is to have a text content control titled:
• 'Client' anywhere you want the client name replicated; and • 'ClientDetails' anywhere you want the client details replicated, coupled with the ContentControlOnExit macro re-coded as: Code:
Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean) Application.ScreenUpdating = False Dim i As Long, StrDetails As String With CCtrl If .Title = "Client" Then If .Type <> wdContentControlDropdownList Then Exit Sub For i = 1 To .DropdownListEntries.Count If .DropdownListEntries(i).Text = .Range.Text Then StrDetails = Replace(.DropdownListEntries(i).Value, "|", Chr(11)) Exit For End If Next With ActiveDocument.SelectContentControlsByTitle("Client") For i = 2 To .Count If CCtrl.Range.Text = CCtrl.PlaceholderText Then .Item(i).Range.Text = "" Else .Item(i).Range.Text = CCtrl.Range.Text End If Next End With With ActiveDocument.SelectContentControlsByTitle("ClientDetails") For i = 1 To .Count .Item(i).Range.Text = StrDetails Next End With End If End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
I broke it. Ok so I deleted the original VBA code (dont worry I saved a copy of it for pasting later if I need to) and deleted the second content control that was showing the customer information. I added two text content controls as described in your comment earlier. Now it keeps popping an error code telling me that "you are not allowed to edit this section because it is protected". When I hit the debug option this is the line that it references.
.Item(i).Range.Text = .Item(1).Range.Text I checked my 3 content controls (drop down content control and two text content controls) and verified that they were all set to allow editing. I am not sure what I did wrong or deleted that I shouldn't have. Please help. I have attached a simplified version of the doc so you can see what I am working with and maybe give better insight. Thank you for all your help. Sincerely, Angela |
#8
|
||||
|
||||
Hi Angela,
Update attached. I've tweaked the code a bit. Note also that you don't need forms protection for this to work.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto Populate Text On Dependent Drop Down Exit | GregStewartPTC | Word VBA | 2 | 02-27-2017 05:37 AM |
How to populate dependent dropdowns and auto-fill text fields simultaneously? | vera | Word VBA | 1 | 10-07-2016 07:41 PM |
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 |
auto populate fields for multiple files w/in folder | jbyrd | Word | 0 | 07-21-2014 07:35 AM |
Auto populate text | help i'm stuck! | Word | 1 | 08-13-2010 11:52 AM |