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.
LEARN MORE: Office 365 Consumer pricing and features
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:
- 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. )
- In the Controls group, click the Legacy Form dropdown and choose Drop-Down Form Field.
- 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.)
SEE: Download: Build your Excel skills with these 10 power tips (TechRepublic)
Now you’re ready to populate the first dropdown with the region items, as follows:
- Right-click the region dropdown field and choose Properties from the resulting submenu.
- In the Dropdown item control, enter North and click Add. Repeat to add South, East, and West.
- 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.
- 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:
- Launch the Visual Basic Editor (VBE) by pressing [Alt]+[F11].
- From the Insert menu, choose Module.
- Enter the PopulateddState sub procedure shown below.
- Click Save on the Standard toolbar and then click Yes.
- Return to the Word document.
Select Case ActiveDocument.FormFields("ddRegion").Result
.Add "New York"
You’re almost done. You need to tell the state dropdown to execute the new procedure you just added, as follows:
- Right-click ddRegion and choose Properties. (Or, click Properties in the Controls group on the Developer tab.)
- From the Exit dropdown, choose PopulateddState.
- Click OK.
Just one last step. Before using the fields, enable protection, as follows:
- Click the Developer tab and click Restrict Editing in the Protect Group.
- In the resulting task pane, click the Allow Only… option under 2. Editing Restrictions and choose Filling In Forms from that option’s dropdown.
- Then, click Yes, Start Enforcing Protection under 3. Start Enforcement.
- 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.