Software

How to populate a dependent dropdown list in Word

Use VBA code to populate a dependent form field in Microsoft Word. Susan Harkins show us how.

Many templates and web forms use Word form fields to solicit information from the user. Occasionally, the selection in one field determines the contents of another. You can hard-code list items in a VBA procedure to populate the secondary (dependent) list. Or, you can retrieve the appropriate list items from a database. The latter is more dynamic, but requires more code and a database. For this example, we'll stick with the simpler solution and hard-code the items.

To illustrate this dependent list solution, we'll populate a primary list with region items: North, South, East, and West. Then, we'll use a VBA Select Case statement to populate a second list with a few states from the selected region. The first step is to create the user form and add a couple of form fields, as follows:

  1. In Word, open a blank document and click the Developer tab. (If you don't know how to enable the Developer tab, read  How to: Show the Developer Tab on the Ribbon. )
  2. In the Controls group, click the Legacy Form dropdown and choose Drop-Down Form Field.
  3. Repeat step 2 to add a second dropdown field.

(If you're still using Word 2003, the instructions are similar, but you'll need to display the Forms toolbar to access the controls. To do so, choose Toolbars from the View menu, and then select Forms.)

Now you're ready to populate the first dropdown with the region items, as follows:

  1. Right-click the region dropdown field and choose Properties from the resulting submenu.
  2. In the Dropdown item control, enter North and click Add. Repeat to add South, East, and West.
  3. In the Bookmark control, enter ddRegion. Don't skip this step - when I receive mail about these types of techniques, more often than not the reader has missed this step. You don't have to rename the dropdown field, you can retain the default, but giving it a descriptive and meaningful name will make the code easier to maintain.
  4. Click OK.

Next, right-click the state dropdown field and name it ddState (using the Bookmark control, as you just did to name the first dropdown ddRegion.) Click OK to close the dialog box.

At this point, you have one populated dropdown named ddRegion and a second dropdown named ddState, that's still empty. To populate ddState, add the VBA procedure below, as follows:

  1. Launch the Visual Basic Editor (VBE) by pressing [Alt]+[F11].
  2. From the Insert menu, choose Module.
  3. Enter the PopulateddState sub procedure shown below.
  4. Click Save on the Standard toolbar and then click Yes.
  5. Return to the Word document.
Sub PopulateddState()
 Select Case ActiveDocument.FormFields("ddRegion").Result
 Case "North"
 With ActiveDocument.FormFields("ddState").DropDown.ListEntries
 .Clear
 .Add "Michigan"
 .Add "Ohio"
 End With
 Case "South"
 With ActiveDocument.FormFields("ddState").DropDown.ListEntries
 .Clear
 .Add "Georgia"
 .Add "Texas"
 End With
 Case "East"
 With ActiveDocument.FormFields("ddState").DropDown.ListEntries
 .Clear
 .Add "New York"
 .Add "Maine"
 End With
 Case "West"
 With ActiveDocument.FormFields("ddState").DropDown.ListEntries
 .Clear
 .Add "California"
 .Add "Oregon"
 End With
 End Select
 End Sub

You're almost done. You need to tell the state dropdown to execute the new procedure you just added, as follows:

  1. Right-click ddRegion and choose Properties. (Or, click Properties in the Controls group on the Developer tab.)
  2. From the Exit dropdown, choose PopulateddState.
  3. Click OK.

Just one last step. Before using the fields, enable protection, as follows:

  1. Click the Developer tab and click Restrict Editing in the Protect Group.
  2. In the resulting task pane, click the Allow Only... option under 2. Editing Restrictions and choose Filling In Forms from that option's dropdown.
  3. Then, click Yes, Start Enforcing Protection under 3. Start Enforcement.
  4. Enter the same password twice, and click OK.

All that's left is to use the fields. Click the region field's dropdown and choose South. Doing so executes the sub procedure (macro) PopulateddStates(), which populates the state dropdown with Georgia and Texas.

Remember, the list items are hard-coded. This method is certainly easy but with its ease comes limitations. It works best with a static list of known items.

Demo Excel files are available to help you understand this technique.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

13 comments
PeggyBall
PeggyBall

This worked GREAT!  Thank you!  I do need to take it a bit further...

I would like to be able to choose a school from the first dropdown then have the full address and phone number, will require 3 lines, be populated directly below the name based on the school selection.  How can I modify the code to accomplish this?

dannypk
dannypk

Is it possible to add another dependent field based on the results of the first dependent field?  

This has been so very helpful!

raicsl
raicsl

An interesting option but not very flexible unless all of the document choices are restricted to Dropdowns. Les

chsouch
chsouch

This is awesome. Is it possible to set this up so a user can select more than one 'state'?

McGnity
McGnity

Thank you for sharing this with us. Simple, clean, efficient and helps a lot.

candzgramma
candzgramma

Way too cool. I'm still using Word 2003 and the process worked perfectly for me. I have created macros in the past using the record function, then gone in and modified them so I have a miniscule knowledge of VBA. Thanks for a new nugget of knowledge.

sdavis
sdavis

The super-sized VBA bibles are impressive. Yet much as a Vegas buffet functions, they fill your plate with info way before you get to the good stuff. VBA in small chunks is edible! My compliments to the Chef! skd76

ravenenok
ravenenok

Nice topic and absolutely right in time

ssharkins
ssharkins

Inherently, this control limits you to one item--think about it. The control displays your choice in the text box portion. There's really no room for more than one. If you need something a bit more flexible, try the ActiveX list box control and set the Multi Select property appropriately. You'll need VBA to control the selection's value, but it's easy enough to do.

ssharkins
ssharkins

Thank you -- glad you were able to work through in 2003 with no problems!

ssharkins
ssharkins

Thanks so much -- I'm glad you liked this one!

ssharkins
ssharkins

Thank you -- I'm glad you found the topic useful!

Editor's Picks