Linked DropDown Formfields

The information in this website is provided without risk or obligation and free of charge. However, if you have benefitted from my efforts here and would like to make a contribution to help me continue and maintain this work then any donation will be greatly appreciated. Please click the adjacent button to access PayPal. Thank you.

This Word Help & Tips page will show you how to link a pair of dropdown formfields so the list displayed in the second field is determined by the selection made in the first field. Additionally, I will show you a work-a-round for the dropdown box 25 item list limit.

Create a new document and add two dropdown formfields. Using the formfield dialog box, I named these fields "PrimaryDD" and "SecondaryDD." Populate the PrimaryDD field with your primary list entries. For this example, I populated the PrimaryDD list with the letters "A, B and C."

You will need to use a macro that fires "on exit" from the primary dropdown field to interpret the PrimaryDD selection and set the list entries in the second dropdown field. Open the VB Editor and paste in the following code:

Sub OnExitDDListA()
Dim oDD As DropDown
Set oDD = ActiveDocument.FormFields("SecondaryDD").DropDown
'Clear previous list
oDD.ListEntries.Clear
'Repopulate list based on user selection
Select Case ActiveDocument.FormFields("PrimaryDD").Result
    Case "A"
        With oDD.ListEntries
             .Add "Apples"
             .Add "Apricots"
             .Add "Artichokes"
        End With
    Case "B"
        With oDD.ListEntries
             .Add "Blueberries"
             .Add "Beets"
             .Add "Brocolli"
        End With
    Case "C"
        With oDD.ListEntries
            .Add "Cherries"
            .Add "Celery"
            .Add "Cilantro"
        End With
End Select
End Sub

TIP: Need help applying macros? See fellow MVP Graham Mayor's Guide for Installing Macros

Set the macro to run on exit using the PrimaryDD form dialog box.

Protect the form and give it a try. When the user exits from the PrimaryDD field the macro fires. First any existing list in the SecondaryDD is cleared. Then the user selection is determined and the SecondaryDD is repopulate with the appropriate list items.

Note: If you simply want to set the value of a single text field based on the selection from a dropdown then you can use something like:

Sub OnExitDDList()
Dim oFFs As FormFields
Set oFFs = ActiveDocument.FormFields
Select Case oFFs("DropDown1").Result
    Case "A"
        oFFs("Text1").Result = "First letter of alphabet"
    Case "B"
        oFFs("Text1").Result = "Second letter of alphabet"
    Case "C"
        oFFs("Text1").Result = "Third letter of alphabet"
End Select
End Sub

This method works until you encounter the 25 item limit for a protected form dropdown list. Consider another pair of dropdown fields. The primary field is named "CountryName" and the secondary fields is named "StateDD1." I populated the primary fields with "USA, Canada and Mexico."

Except for the 25 item limit, the following code set to run on exit from the CountryName dropdown field would do nicely for this situation:

Sub FillStateDD()
Dim myArray1() As String
Dim myArray2() As String
Dim myArray3() As String
Dim i As Long
myArray1 = Split("AB BC MB NB NL NS NT NU ON QC RE SK YT")
myArray2 = Split("AGU BCN BCS CAM CHP CHH COA COL DIF DUR" _
                      & "GUA GRO HID JAL MEX MIC MOR NAY NLE OAX PUE" _
                      & " QUE ROO SLP SIN SON TAB TAM TLA VER YUC ZAC")
myArray3 = Split("AL AK AS AZ AR CA CO CT DE DC FM FL GA GU" _
                      & "HI ID IL IN IA KS KY LA ME MH MD MA MI MN MS" _
                      & "MO MT NE NV NH NJ NM NY NC ND MP OH OK OR PW" _
                      & "PA PR RI SC SD TN TX UT VT VI VA WA WV WI WY")
ActiveDocument.FormFields("StateDD").DropDown.ListEntries.Clear
Select Case ActiveDocument.FormFields("CountryName").Result
    Case "Canada"
        For i = 0 To UBound(myArray1)
            ActiveDocument.FormFields("StateDD").DropDown.ListEntries.Add myArray1(i)
        Next i
    Case "Mexico"
        For i = 0 To UBound(myArray2)
            ActiveDocument.FormFields("StateDD").DropDown.ListEntries.Add myArray2(i)
        Next i
    Case "USA"
        For i = 0 To UBound(myArray3)
            ActiveDocument.FormFields("StateDD").DropDown.ListEntries.Add myArray3(i)
        Next i
    End Select
End Sub

If you protect your form and select "Canada" the code with run without error. However, if you select Mexico or the USA the code generates the following error:

The dropdown list limit is 25. It can not be increased to 31 or to 50 or 100 or even 26. The limit is 25!

The work around is to use a Userform and listbox. A listbox is not limited to 25 items. To demonstrate this method I use a dropdown field named Country_Name and a text form field named txtState. I assigned the following macro to run "on entry" to the txtState field:

Sub CallFillStateDD()
StateProv.Show
End Sub

This simple code displays a UserForm named StateProv that I added to the project that looks like this:

The UserForm initialization code determines the user selection in the Country_Name field and populates the UserFrom ListBox accordingly. When the user makes a selection in the UserForm, the UserForm CommandButton_Click event displays the selection in the document text form field txtState and closes the UserForm:

Private Sub UserForm_Initialize()
Dim myArray1() As String
Dim myArray2() As String
Dim myArray3() As String
Dim i As Long
myArray1 = Split("AB BC MB NB NL NS NT NU ON QC RE SK YT")
myArray2 = Split("AGU BCN BCS CAM CHP CHH COA COL DIF DUR" _
                      & "GUA GRO HID JAL MEX MIC MOR NAY NLE OAX PUE" _
                      & " QUE ROO SLP SIN SON TAB TAM TLA VER YUC ZAC")
myArray3 = Split("AL AK AS AZ AR CA CO CT DE DC FM FL GA GU" _
                      & "HI ID IL IN IA KS KY LA ME MH MD MA MI MN MS" _
                      & "MO MT NE NV NH NJ NM NY NC ND MP OH OK OR PW" _
                      & "PA PR RI SC SD TN TX UT VT VI VA WA WV WI WY")
Me.ListBox1.Clear
Select Case ActiveDocument.FormFields("Country_Name").Result
    Case "Canada"
        Me.ListBox1.List = myArray1
    Case "Mexico"
        Me.ListBox1.List = myArray2
    Case "USA"
        Me.ListBox1.List = myArray3
End Select
End Sub

Private Sub CommandButton1_Click()
ActiveDocument.FormFields("txtState").Result = Me.ListBox1.Text
Unload Me
End Sub

Bonus Tip: Need help with creating a UserForm? See my Custom VBA Message Box tips page

Looking for something else?