Help with FormFields (One ComboBox influencing another)

[DavG63]

Morning all

I have a Userform at the moment with two ComboBoxes "Area" and "Supplier".

I'd been thinking about how best to design my Userform, so that when the user selects an Area, that the list presented to them in Supplier is truncated to just those entries for that region.

I have 33 different Areas and my initial thought was to have 33 separate ComboBoxes which would be hardcoded with the Suppliers for each area, and would become visible or invisible depending upon the Area selected. Whilst this would no doubt work, it would be a complete pain to set up and would take me ages to code.

So, I did what everyone does when faced with a question they don't know the answer to and Googled and found an article linking me to a code which used FormFields. The code that it used was:-

If ActiveDocument.FormFields("DropDown1").DropDown.Value = 0 Then        ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries.Clear
        Exit Sub
     End If
     Select Case ActiveDocument.FormFields("Dropdown1").Result
        Case "North"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Iceland"
                .Add "Finland"
                .Add "Norway"
                .Add "Sweden"
            End With
        Case "South"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Greece"
                .Add "Italy"
                .Add "Portugal"
                .Add "Spain"
            End With
        Case "East"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Czechoslovakia"
                .Add "Hungary"
                .Add "Poland"
                .Add "Romania"
            End With
        Case "West"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Belgium"
                .Add "Denmark"
                .Add "France"
                .Add "Netherlands"
            End With
     End Select

Whilst it doesn't match specifically what I wanted, if I changed the names and added in another 30 or so variations this would work perfectly for selecting the Area and generating the Suppliers in the second ComboBox.

Unfortunately the article wasn't clear on how I set up Formfields for "Dropdown1" and "Dropdown2". Can anyone assist? For info, my document has no bookmarks and it's not linked to a database - I'd prefer to hard code the options into the document itself.

Thanks very much

Dav

[gmaxey]

Doc1.docmWithout knowing your version of Word I can't provide exact steps. Assuming Word 2007 or higher, the formfields Dropdown1 and Dropdown2 are inserted using the Developer Tab>Controls Group>Legacy Forms>Dropdown list control. When inserted their default names will be Dropdown1 and Dropdown2 (which you can change). Double click on Dropdown1 to open its dialog. Add the list members and if you want change the name then set the macro to run on exit. Close the dialog and protect the form and your are done.

If you are using Word07 or higher, then consider content controls instead. See attached (Doc1.docm) at the start of the post.

[gmayor]

If this is a userform, you don't need form fields. You can selectively show the items in the userform - see the linked example
http://www.gmayor.com/Forum/Example Userform.docm

[DavG63]

Greg, Graham

Thanks both for your responses I appreciate your help.

For reference, I'm using Word 2010 and Graham, you're correct that I'm using a Userform, rather than having drop downs which are within the body of the document itself. I tried using the link you provided but unfortunately it didn't appear to lead anywhere and I got an error.

Thus far I've written the code (appropriated from another post on the forum) that will place an array into my ComboBox for "Area":-

Dim myArray() As String  'Use Split function to return a zero based one dimensional array.
  myArray = Split("Aberdeen|Airdrie|Alloa|Ayr|Banff|Campbeltown|Dumbarton|Dumfries|Dundee|Dunfermline|" _
             & "Dunoon|Edinburgh|Elgin|Falkirk|Forfar|Fort William|Glasgow|Greenock|" _
             & "Hamilton|Inverness|Jedburgh|Kilmarnock|Kirkcaldy|Kirkwall|Lanark|Lerwick|" _
             & "Livingston|Lochmaddy|Oban|Paisley|Perth|Peterhead|Portree|Selkirk|Stornoway|" _
             & "Stranraer|Tain|Wick|", "|")
  'Use .List method to populate ComboBox.
  ComboBox9.List = myArray
lbl_Exit:

I now would like to have different options available in Supplier for every one of the above locations.

If it's simply not possible please let me know and I'll go back to my original idea of having 33 invisible ComboBoxes, each coded with the Suppliers for one area, and which appear and disappear as selected.

Thanks very much

Dav

[DavG63]

It occurred to me moments after posting that I could probably just do this

If ComboBox9.Value = "Aberdeen" ThenMe.ComboBox11.List = Array("SupplierOne", "SupplierTwo", "SupplierThree")
Else
End If
If ComboBox9.Value = "Airdrie" Then
Me.ComboBox11.List = Array("SupplierOne", "SupplierFour", "SupplierNine")
End If

Whilst this appears to work, it's probably not the most elegant solution.

[gmayor]

The link seems to have separated the last part. It should be http://www.gmayor.com/Forum/Example Userform.docm

[gmaxey]

Dav,

You example code brings back fond memories. I lived three years at 56 Victoria Rd. Dunoon Scotland from '88 to '91. Stomped around many of the towns in your list mainly looking for a single malt better than the ones in previous towns. Laugavulin is hard to beat!

You certainly don't need to 35 combo boxes as you've discovered or that discombobulated If... ElseIf ... construction. The tedious part is sorting out an defining your initial data structure. Once that is done it is a walk in the sun.

I've been tinkering and collaborating with Andreas Killer on a process for cascading comboboxes many levels deep. It is still a work in progress but I'm coming to the conclusion that a CustomXMLPart isan ideal place to store the component parts in Word. Here is some sample code for a paired down process:

This ensures the data is current and displays the form. Place it in a standard code module. The form has two comboboxes name comboArea and combSupplier

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oFrm As UserForm1
  Add_UPdateXMLPart
  Set oFrm = New UserForm1
  oFrm.Show
  Unload oFrm
  Set oFrm = Nothing
lbl_Exit:
  Exit Sub
  
End Sub

Sub Add_UPdateXMLPart()
Dim strXML As String
Dim oXMLPart As CustomXMLPart
  On Error Resume Next
  Set oXMLPart = ActiveDocument.CustomXMLParts.SelectByNamespace("Area Data").Item(1)
  If Not oXMLPart Is Nothing Then
    oXMLPart.Delete
  End If
  On Error GoTo 0
  strXML = "<Data xmlns='Area Data'><Area>North<Supplier>A</Supplier><Supplier>B</Supplier></Area>" _
                                 & "<Area>East<Supplier>C</Supplier><Supplier>D</Supplier></Area>" _
                                 & "<Area>South<Supplier>E</Supplier><Supplier>F</Supplier></Area>" _
                                 & "<Area>West<Supplier>G</Supplier><Supplier>H</Supplier></Area>" _
                                 & "<Area>Dunoon<Supplier>The Breas</Supplier><Supplier>The Brewery</Supplier><Supplier>The Clansman</Supplier></Area></Data>"
  Set oXMLPart = ActiveDocument.CustomXMLParts.Add
  oXMLPart.LoadXML strXML
End Sub

All you have do is define the XML sting to match your need. The tedious part



This controls the form. Place it in the form module:

Option Explicit
Private m_oXMLPart As CustomXMLPart
Private m_oXMLNodes As CustomXMLNodes
Private Sub comboArea_Change()
Dim lngIndex As Long
  With comboSupplier
    .Clear
    Set m_oXMLNodes = m_oXMLPart.SelectNodes("/ns0:Data/ns0:Area[" & comboArea.ListIndex + 1 & "]/*")
    For lngIndex = 1 To m_oXMLNodes.Count
      .AddItem m_oXMLNodes(lngIndex).Text
    Next
 End With
lbl_Exit:
  Set m_oXMLNodes = Nothing
  Exit Sub
End Sub

Private Sub UserForm_Initialize()
Dim lngIndex As Long
  Set m_oXMLPart = ActiveDocument.CustomXMLParts.SelectByNamespace("Area Data").Item(1)
  Set m_oXMLNodes = m_oXMLPart.SelectNodes("/ns0:Data/ns0:Area")
  comboArea.MatchRequired = True
  For lngIndex = 1 To m_oXMLNodes.Count
    comboArea.AddItem m_oXMLNodes(lngIndex).ChildNodes(1).Text
  Next
lbl_Exit:
  Set m_oXMLNodes = Nothing
  Exit Sub
End Sub