Solved: Conditional Drop Down with a twist

[dragon576]

I've seen and used examples of conditional drop down boxes where based on the first selection, another drop down field is populated with new (different) data, but I need to do something slightly different.

Four drop down lists each with an associated formfield. The first (and currently all) drop down list has the values 1 to 4 populated, and based on the selection the associated text form field is populated with "Very Good", "Good", "Average" or "Poor".

So far so good. The problem I am trying to solve is as follows.

If I select 2 (as an example) in the first drop down list, I want the second populated with 1,3 and 4 only.

If I then select 3 from the second drop down list (again as an example), the third drop down list should only be populated with 1 and 4. Etc

So the questions I have are:
1. How to achieve this ?
2. How to ensure that if the user starts on a different drop down list than the first (for example from the bottom up, or from the middle), the same function applies ?

I have attached an example word doc with VBA.

Thanks
Doug

[fumei]

Will respond to the later questions...later. However, I would like to point out a way to do the first step easier.Option Explicit

Sub Rating1()
' Drop Down 1
Dim oFF As FormFields
Dim arrPreference(3) As String
arrPreference(0) = "First Preference"
arrPreference(1) = "Second Preference"
arrPreference(2) = "Third Preference"
arrPreference(3) = "Fourth Preference"
Set oFF = ActiveDocument.FormFields
oFF("text1").Result = _
arrPreference(oFF("dropdown1").DropDown.Value - 1)
End Sub

Sub Rating2()
' Drop Down 2
Dim oFF As FormFields
Dim arrPreference(3) As String
arrPreference(0) = "First Preference"
arrPreference(1) = "Second Preference"
arrPreference(2) = "Third Preference"
arrPreference(3) = "Fourth Preference"
Set oFF = ActiveDocument.FormFields
oFF("text2").Result = _
arrPreference(oFF("dropdown2").DropDown.Value - 1)
End Sub

' etc etcBy using an array you can make the text formfield result simply equal to the array value. Minus 1 of course - unless you make the array 1 based, rather than 0 based.

You could also make the array global, to be used by all procedures. You do then have to declare it globally and populate it though.

[fumei]

So the questions I have are:
1. How to achieve this ?
2. How to ensure that if the user starts on a different drop down list than the first (for example from the bottom up, or from the middle), the same function applies ?You achieve this by writing out the logic.

Although, question #2 is much harder, and you would have to precisely define what you mean.

Say DD1 = 2 THEN DD2(items) = 1, 3, 4

That is OK. But what = precisely - do you mean by "the same function applies". Do you mean when the document opens? In which case all DD have 1, 2, 3, 4.

Do you mean say all have 1 - 4, but the user selects DD3 first and selects 1. Now...what does DD1 have? 2, 3, 4? Do all the rest have 2, 3, 4?

What I am asking is: you are trying to get a reductive logic going here? ONE (and only one) of the DD can have 1, and as the user progresses the options offered on the other DD (no matter which one it is) decreases.

[fumei]

Oh...and i noticed you probably did a copy and paste of the formfields in your attached document.

Please note the name of the formfield in the second batch (for Paris).

Notice something??

NO names! You must be very careful with copying formfields. New names for them are not generated.

[fumei]

Wooo boy. It can be done, but the logic is convoluted.

[fumei]

lordy lordy lordy....this is very very complex. I have it that the OTHER dropdowns will reflect what the current dropdown is NOT.

If DD2 = 2 then DD1, DD3, DD4 will have 1, 3, 4.

But to add further logic to make sure it is truly cascading reductive logic means checking every value for every dropdown each time.

Again, it CAN be done. But...hmmmm, it ain't my project and I have done enough for now.

[dragon576]

The more I think about this the more complex it seems, all to make it simpler for the end user. I have thought of a possible solution avoiding drop down lists.

If I use some kind of matrix of checkboxes for the user to select the value and can get a value for a checkbox (or a value in a form field), it may be possible to calculate the sum and put in some validation rules to ensure the user only selects one per line, as well as populating the associated text box. I need to have a think about the approach in more depth.

Alternatively I can just get the user to fill in the box manually. Now that really could be doomed to failure from the start.

Doug

[fumei]

Actually, what it needs is a full logic table.

DD1 - if 1, what is DD2, DD3, DD4? (This may be easy).
DD1 - if 2...what is DD2? DD3? DD4?

etc. etc. etc.

DD4 - if 2...what is DD1?...well it depends, DD1 may already BE 1, in which case, you do NOT want to change it. But if it is 2, then you do, but to what? Do you make the choices 1, 3, 4? Only 3, 4? You have to make sure there IS a 1 somewhere. Right?

So...yeah, it is convoluted. However, it is only logic, and of course can be developed. It may not be worth it. So...yup, time for a re-think.

[fumei]

Here is a possible direction for you to go. Still take a bit of work. I am attaching a demo of a userform, for the city of Paris. The userform displays on document opens. Here is what it does.

It has a frame with the four choices (zoo, museum, shopping, nightlife). There is a Next button. The frame starst out with its caption being "Paris - First Preference".

Clicking Next causes: if no selection has been made, a message stating so is displayed; if a selection is made: adds the selected option to an array of preferences (Paris()); increments a counter; clears all option buttons; recaptions the frame to "Second Preference".

User keeps selecting options and clicks next. Label of the frame increments ("Paris Third Preference", Paris Fourth Preference")

On reaching all four prefrences, the Next button changes its caption to "Reset?". The frame with options is made invisible, and a label message takes its place. the labels displays the choices the user has made. If the "Reset?" button is now clicked, it will make the message invisible; make the frame visible; reset the frame to Paris First Preference, clear the array and the user starts again. Also on reaching a count of the four preferences, a new commanbutton is made visible "Done"

"Done" is made invisible if the user chooses "Reset".

If user clicks "Done", four bookmarks in the document are used to take the text (the user preferences) from the array.

Here is the code. In a code module:Public Paris()
Public FrameCaptions(3)

In the userform module:Option Explicit


Public iListCount As Integer

Private Sub cmdNextChoice_Click()
Dim strPrefixMessageText(3) As String
Dim var
Dim i As Integer
Dim ctl As Control
Dim strChoices As String
If cmdNextChoice.Caption = "Reset?" Then
Call ResetFrame
Else
strPrefixMessageText(0) = "First: "
strPrefixMessageText(1) = "Second: "
strPrefixMessageText(2) = "Third: "
strPrefixMessageText(3) = "Fourth: "

i = iListCount
If iListCount < 4 Then
For Each ctl In fraParis.Controls
If fraParis.Controls(ctl.Name).Value = _
True Then
ReDim Preserve Paris(iListCount)
Paris(iListCount) = _
fraParis.Controls(ctl.Name).Caption
iListCount = iListCount + 1
End If
Next

If i < iListCount Then
If iListCount < 4 Then
fraParis.Caption = _
FrameCaptions(iListCount)
Call ClearButtons
End If
ElseIf i = iListCount Then
MsgBox "No selection was made."
Exit Sub
End If
End If
If iListCount = 4 Then
For var = 0 To iListCount - 1
strChoices = strChoices & vbCrLf & _
strPrefixMessageText(var) & _
Paris(var)
Next
lblMessage.Caption = "Preferences have been made." & _
vbCrLf & vbCrLf & strChoices
fraParis.Visible = False
lblMessage.Visible = True
cmdOK.Visible = True
cmdNextChoice.Caption = "Reset?"
End If
End If
End Sub
Sub ClearButtons()
Dim ctl As Control
For Each ctl In fraParis.Controls
fraParis.Controls(ctl.Name).Value = False
Next
End Sub
Sub ResetFrame()
ReDim Preserve Paris(0)

Call ClearButtons
lblMessage.Visible = False
fraParis.Visible = True
fraParis.Caption = FrameCaptions(0)
cmdNextChoice.Caption = "Next"
iListCount = 0
End Sub

Private Sub cmdOK_Click()
ActiveDocument.Bookmarks("First"). _
Range.Text = Paris(0)
ActiveDocument.Bookmarks("Second"). _
Range.Text = Paris(1)
ActiveDocument.Bookmarks("Third"). _
Range.Text = Paris(2)
ActiveDocument.Bookmarks("Fourth"). _
Range.Text = Paris(3)

Unload Me
End Sub

Private Sub UserForm_Initialize()
FrameCaptions(0) = "Paris First Preference"
FrameCaptions(1) = "Paris Second Preference"
FrameCaptions(2) = "Paris Third Preference"
FrameCaptions(3) = "Paris Fourth Preference"
fraParis.Caption = FrameCaptions(0)
cmdOK.Visible = False
lblMessage.Visible = False
cmdNextChoice.Caption = "Next"
iListCount = 0
End Sub

[fumei]

oh, and next time...yes I will comment it.

[fumei]

Code in file is now commented. Also added an escape button. As userform loads on document open, you may (after a couple of times) be rather bored with that. the "Forget it!" will unload the form.

[dragon576]

I think my learning curve just got steeper :)

Thanks for the help. I see where that takes me.

Doug

[fumei]

That is what I was hoping for. Don't worry about the learning curve. IMHO, the coding part of things is far less significant than knowing what is possible, and trying to come up with good design. The biggest hurdle for newcomers is the not knowing what is possible.

Also, IMHO, the demo I attached will give a lot of you folks ideas. The use of controls being visible/invisible depending on current conditions is a very useful part of userform design.

That and using ONE button to do TWO things. It is not limited to two, either. The Next button changing to a ReSet button is a good example of efficient design. Most people would use two separate buttons. Often, this is not needed.

The hardest part is NOT the coding. The hardest part is building the logic.