DavG63
12-10-2015, 04:19 AM
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
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