none
Autopopulate form field based on drop-down RRS feed

  • Question

  • This is not an uncommon topic but I could not find a resolution that matches my situation.

    My current code does well in creating a drop-down in one form field, based on a selection made in another:

    Sub PopulateLocation()

     Select Case ActiveDocument.FormFields("Department").Result

        Case "Department 1"
            With ActiveDocument.FormFields("Location").DropDown.ListEntries
                .Clear
                .Add "         "  ' to clear a field
                .Add "123 First Avenue"
                .Add "456 Second Street"
                .Add "789 Third Boulevard"
            End With

        Case "Department 2", "Department 3", "Department 4", "Department 5"
            With ActiveDocument.FormFields("Location").DropDown.ListEntries
                .Clear
                .Add "         "  ' to clear a field
                .Add "123 Fourth Crescent"
            End With

        etc. . . .

    My question pertains to the second "Case" above.  I would like the "Location" form field to automatically populate with "123 Fourth Crescent" when it is only one option for a case, saving the user the trouble of having to complete the Location form field.

    Any help is appreciated.

    Thank-you!

    Tuesday, September 29, 2015 5:53 AM

Answers

  • In that case, try:

    Sub PopulateLocation()
    With ActiveDocument
      Select Case .FormFields("Department").Result
        Case "Department 1"
          With .FormFields("Location")
            With .DropDown.ListEntries
              .Clear
              .Add "         "  ' to clear a field
              .Add "123 First Avenue"
              .Add "456 Second Street"
              .Add "789 Third Boulevard"
            End With
            .Enabled = True
          End With
        Case "Department 2", "Department 3", "Department 4", "Department 5"
          With .FormFields("Location")
            With .DropDown.ListEntries
              .Clear
              .Add "123 Fourth Crescent"
            End With
            .Result = .DropDown.ListEntries(1).Name
            .Enabled = False
          End With
        Case Else
          With .FormFields("Location")
            .DropDown.ListEntries.Clear
            .Enabled = False
          End With
      End Select
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by R.Shaw Wednesday, September 30, 2015 8:34 PM
    Wednesday, September 30, 2015 3:18 AM

All replies

  • Unless there is a need for the user to choose between multiple addresses for the same department, you don't need a formfield or a macro for the output. All you need is to have the Department dropdown's 'calculate on exit' property set and a series of formula fields coded along the lines of:

    {IF{REF Department}= "Department 1" "123 First Avenue"}{IF{REF Department}= "Department 2" "456 Second Street"}{IF{REF Department}= "Department 3" "789 Third Boulevard"}

    Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. The spaces represented in the field construction are all required.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, September 30, 2015 1:24 AM
  • Thanks for the reply.  I need both the ability to choose between multiple departments with multiple locations (for instance, in my example above, Department 1 has 3 addresses where the user may be working) and to choose between multiple departments with single locations (in my example above, if someone identifies as working in Department 2, there are no multiple locations and so to make it more convenient for the user).

    To explain more clearly, several employees are working across several departments; some departments have multiple locations, others do not; and all these employees use the same form.

    I appreciate your help.  I hope I've understood your reply correctly.  Is this explanation different than what you thought?

    If your advice is the same, how do I accomplish a combination of dependent drop-downs with the advice you provided?

    Wednesday, September 30, 2015 2:34 AM
  • In that case, try:

    Sub PopulateLocation()
    With ActiveDocument
      Select Case .FormFields("Department").Result
        Case "Department 1"
          With .FormFields("Location")
            With .DropDown.ListEntries
              .Clear
              .Add "         "  ' to clear a field
              .Add "123 First Avenue"
              .Add "456 Second Street"
              .Add "789 Third Boulevard"
            End With
            .Enabled = True
          End With
        Case "Department 2", "Department 3", "Department 4", "Department 5"
          With .FormFields("Location")
            With .DropDown.ListEntries
              .Clear
              .Add "123 Fourth Crescent"
            End With
            .Result = .DropDown.ListEntries(1).Name
            .Enabled = False
          End With
        Case Else
          With .FormFields("Location")
            .DropDown.ListEntries.Clear
            .Enabled = False
          End With
      End Select
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by R.Shaw Wednesday, September 30, 2015 8:34 PM
    Wednesday, September 30, 2015 3:18 AM
  • Looks like it's working!  Thank-you very much for your assistance!
    Wednesday, September 30, 2015 8:35 PM