Dropdown population query...

[Tecnik]

Hi There,

I've made a start on some VBA that automatically populates dropdowns B1-B6, with several options, from a choice made in dropdown A, which has several options.
In turn, the selection made in dropdowns B1-B6 then populates a textfield C1-C6 with some pre-defined text.

Am I correct in thinking a 2d array would be the best way to implement a solution. So, the options selected from dropdown A and dropdown B would give you the index of the pre-defined text held in the 2d array which is used to populate text formfield C1-C6.

Any help on this would be much appreciated.

Many thanks in advance.

Nick

[macropod]

Hi Nick,

Certainly a 2D array would be flexible enough to handle this. Equally, though, since you need to populate the array (eg from a range of cells - which could be regarded as an array - or from a series of delimited text strings), you could perhaps just as easily use a Case statement to choose which delimited text string to popoulate a variable with, then use that variable to populate the dropdown via a loop with a Split statement. The potential advantage of this approach is that you don't then have to test the array for empty elements.

[Tecnik]

Thanks for the reply and for your assistance.

I think the 'array' version would be easier for me to implement. Can anyone point me to any examples please?

Could this sort of solution be implemented if there were several repetitions of dropdown B?

Thanks,

Nick

[macropod]

Hi Nick,

Here are two sample modules - one using an array to hold the variables and one using text strings. They work via an 'on exit' macro attached to a checkbox formfield named 'Check1' to populate a Dropdown formfield named 'Dropdown1'.

If you try to delete one or more entries from an element in the array dor Demo1, you soon find that the code falls over (ie you need extra processing to handle options that don't always lead to the same number of dropdown entries).

Sub Demo1()
Dim Chkstate As Integer
Dim i As Integer
Dim Drop1Options(2, 7) ' Array
For i = 0 To UBound(Drop1Options, 2)
Drop1Options(1, i) = Split("None,1.25,1.4,1.5,1.6,1.7,2,3", ",")(i)
Drop1Options(2, i) = Split("0,4,6,7,8,9,12,19", ",")(i)
Next i
Chkstate = 2
With ActiveDocument
If .FormFields("Check1").CheckBox.Value = False Then Chkstate = 1
With .FormFields("Dropdown1").DropDown
With .ListEntries
.Clear
For i = 0 To UBound(Drop1Options, 2)
.Add Drop1Options(Chkstate, i)
Next
End With
.Default = 1
.Value = .Default
End With
End With
End Sub

Sub Demo2()
Dim i As Integer
Dim DropList As String
With ActiveDocument
Select Case .FormFields("Check1").CheckBox.Value
Case True
DropList = "None,1.25,1.4,1.5,1.6,1.7,2,3"
Case False
DropList = "0,4,6,7,8,9,12,19,21,23,25"
End Select
With .FormFields("Dropdown1").DropDown
With .ListEntries
.Clear
For i = 0 To UBound(Split(DropList, ","))
.Add Split(DropList, ",")(i)
Next
End With
.Default = 1
.Value = .Default
End With
End With
End Sub
For the Demo2 sub, which inserts different number of entries in the DropDown formfield according the to checkbox state, I didn't really need a Case statement, since there's only two conditions (a simple IF test would have done).

Re:
Could this sort of solution be implemented if there were several repetitions of dropdown B?
Yes, you can do that by adding a second 'With .FormFields("Dropdown#").DropDown ... End With' routine to the code, where 'Dropdown#' is your DropDown formfield's bookmark name. And, if you add multiple 'DropList' entries to the Case statement in Demo2 (eg DropList1, DropList2, etc), each of the dependent DropDown formfields can have their own content, instead of sharing the same content.