Word Smart Dropdown Box

[tweek312]

I need to have a word dropdown box which works in tandom with another dropdown. Sort of a menu system that allows the first selection to change the options for the second selection. The first selection is an error type and the second selection is a location.

For example:

If I select "Incorrect Pricing" from the first dropdown, the options for the second dropdown change to a specified set of "Locations" where a pricing error could occur.

The "Error Type" dropdown has approximately 15 options, and the "Location" dropdown will have anywhere from two to eight options.

Any Suggestions?

 

[Dreamboat]

Oh yeah...

Courtesy of fumei!

http://www.vbaexpress.com/kb/getarticle.php?kb_id=5

Excel VBA Certification is Finally Here!

http://www.vbaexpress.com/forum

Check out this bar:

http://www.ArtBarFtLauderdale.com

 

[fumei]

The aeticle Anne mentioned is pretty basic, but gives you an idea. If you have a more complicated situation, please post back here. However, give SPECIFIC details.

For example: "dropdown" is not good enough. What KIND of dropdown? Is it an ActiveX dropdown or a formfield dropdown?

In any case, yes, absolutely, you can have conditionakl logic that resets the dropdown B list (of whatever type) depending on the result of a choice made in dropdown A.

Can do.

Gerry

 

[Dreamboat]

/off-topic

Gerry...how are you with merge form fields?
Can you email me?


Excel VBA Certification is Finally Here!

http://www.vbaexpress.com/forum

Check out this bar:

http://www.ArtBarFtLauderdale.com

 

[tweek312]

The code provided does work however, It will not work if I have more than the two "dropdowns"... Why? And how can I modify it to work with my other dropdowns?

Thanks
tweek

 

[tweek312]

Nevermind... I figured out that all of the dropdowns must be named for it to work properly.

 

[fumei]

AH HA!!!!!!!

Now you know why I always rant about explicit naming of objects! It is ALWAYS a good idea to explicitly name objects.

Here is one of the reasons.

Say you have three text form fields, and you keep the default names Word assigns to them ..Text1, Text2, Text3.

Say you have some conditional logic, such as:

If Text1.Result = "foobar" Then Text3.Result = "Ta-Da"

Then, for some reason, some design reason, you MOVE the formfield Text1 to after Text3. Word assign those names in the order they exost in the document]/b]. So.....

What was Text1 is now Text3;
What was Text3 is now Text2;
What was Text2 is now Text1

Your conditional logic is now messed up. If the object are EXPLICITLY named this will never happen.

Gerry

 

[tweek312]

What in this code determines the default or topmost entry?

Option Explicit 
 
Sub FirstFieldExit() 
     
    Dim Fruits(3) As String 
    Dim Veggies(3) As String 
    Dim Meat(3) As String 
    Dim i As Integer 
    Dim var 
     
     'Create your multiple lists here (dropdown #2)
     
    Fruits(0) = "Apples" 
    Fruits(1) = "Peaches" 
    Fruits(2) = "Pears" 
    Fruits(3) = "Bananas" 
     
    Veggies(0) = "Green Beans" 
    Veggies(1) = "Corn" 
    Veggies(2) = "Lettuce" 
    Veggies(3) = "Squash" 
     
    Meat(0) = "Beef" 
    Meat(1) = "Chicken" 
    Meat(2) = "Pork" 
    Meat(3) = "Veal" 
     
     'Use the value of the dropdown to case select condition
     
    Select Case ActiveDocument.FormFields("Dropdown1").DropDown.Value 
         
         
         'For each one of these cases, change the    "   .Add Name:= part to be
         'one of the options in your #1 dropdown box
         
    Case 1 
        ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear 
        For var = 1 To 4 
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Fruits(i) 
            i = i + 1 
        Next 
        ActiveDocument.FormFields("Result").DropDown.Value = 1 
    Case 2 
        ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear 
        For var = 1 To 4 
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Veggies(i) 
            i = i + 1 
        Next 
        ActiveDocument.FormFields("Result").DropDown.Value = 1 
    Case 3 
        ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear 
        For var = 1 To 4 
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Meat(i) 
            i = i + 1 
        Next 
        ActiveDocument.FormFields("Result").DropDown.Value = 1 
    End Select 
End Sub

 

[fumei]

If I understand correctly, you are asking for what makes the visible top entry in a formfield dropdown. Yes?

Formfield dropdowns are a bit different than other dropdown controls.

Say you have a FormField dropdown, named Fruits. It has the following items:

Apples
Peaches
Pears
Bananas

You can set item #1 as follows:

ActiveDocument.FormFields("Fruits").DropDown.ListEntries.Item(1).Name = "Yaba daba doo"

Note that this does NOT add "Yaba daba doo" to the list. It CHANGES Item #1 to "Yaba daba doo".

Hope this helps.

Gerry
See my Paintings and Sculpture

 

[tweek312]

I have got the dropdowns the way that I want them. Now I need to know how to use a quadruplicated set of dropdowns. I at first created a new set of cases for the additional dropdowns however, an erroer occurs under a very specific occasion. I get the error when I change the selections of the dropdowns more than once and only if I change them to the same "selection" at one time and back. My real issue is getting all four sets of dropdowns working off of the same set of cases.

Any suggestions?

Option Explicit
 
Sub FirstFieldExit()
     
    Dim Numerics(3) As String
    Dim Quantity(5) As String
    Dim Description(3) As String
    Dim Dates(3) As String
    Dim Codes(3) As String
    Dim Costs(2) As String
    Dim NoError(0) As String
    Dim i As Integer
    Dim var
     
     'Create your multiple lists here (dropdown #2)
    
   ' Veggies(0) = "Green Beans"
   ' Veggies(1) = "Corn"
   ' Veggies(2) = "Lettuce"
   ' Veggies(3) = "Squash"
     
    Numerics(0) = "Special Packaging Instructions"
    Numerics(1) = "Batch Header"
    Numerics(2) = "Data Sample Details"
    Numerics(3) = "Other"
     
    Quantity(0) = "700 Notes"
    Quantity(1) = "Bill Of Materials"
    Quantity(2) = "Sales Header"
    Quantity(3) = "Shipper"
    Quantity(4) = "Active Order Text"
    Quantity(5) = "Line Items"
                
    Description(0) = "Component"
    Description(1) = "Source"
    Description(2) = "Header"
    Description(3) = "Active Order Text"
    
    Dates(0) = "Shipper"
    Dates(1) = "Active Order Text"
    Dates(2) = "700 Notes"
    Dates(3) = "Sales Header"
    
    Codes(0) = "I/IQ1"
    Codes(1) = "Carton Label"
    Codes(2) = "Sales Header"
    Codes(3) = "700 Notes"
    
    Costs(0) = "1/IQ1"
    Costs(1) = "Line Items"
    Costs(2) = "Offload Information"
    
    NoError(0) = "N/A"
    
   
     
     'Use the value of the dropdown to case select condition
     
        Select Case ActiveDocument.FormFields("Dropdown1").DropDown.Value
         
         
         'For each one of these cases, change the    "   .Add Name:= part to be
         'one of the options in your #1 dropdown box
         
    Case 1
        ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Numerics(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result").DropDown.Value = 1
    Case 2
        ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
        For var = 1 To 6
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Quantity(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result").DropDown.Value = 1
    Case 3
        ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Description(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result").DropDown.Value = 1
    Case 4
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Dates(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result").DropDown.Value = 1
    Case 5
           ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Codes(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result").DropDown.Value = 1
    Case 6
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
        For var = 1 To 3
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Costs(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result").DropDown.Value = 1
    Case 7
                   ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
        For var = 1 To 1
            ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=NoError(i)
            i = i + 0
        Next
        ActiveDocument.FormFields("Result").DropDown.Value = 1
    End Select
    
        Select Case ActiveDocument.FormFields("Dropdown2").DropDown.Value

    Case 1
        ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=Numerics(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1
    Case 2
        ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 6
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=Quantity(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1
    Case 3
        ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=Description(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1
    Case 4
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=Dates(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1
    Case 5
           ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=Codes(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1
    Case 6
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 3
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=Costs(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1
    Case 7
                   ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 1
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=NoError(i)
            i = i + 0
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1
    End Select

End Sub

 

[fumei]

1. Could you please (I know it takes a little time and effort) remove the spaces from the code, so it lines up a bit better. It is hard to read. For example.

Your post:

        Select Case ActiveDocument.FormFields("Dropdown2").DropDown.Value

    Case 1
        ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
        For var = 1 To 4
            ActiveDocument.FormFields("Result2").DropDown.ListEntries.Add Name:=Numerics(i)
            i = i + 1
        Next
        ActiveDocument.FormFields("Result2").DropDown.Value = 1

Cleaned up:

Select Case ActiveDocument.FormFields("Dropdown2").DropDown.Value

Case 1
  ActiveDocument.FormFields("Result2").DropDown.ListEntries.Clear
  For var = 1 To 4
    ActiveDocument.FormFields("Result2").DropDown_
       .ListEntries.Add Name:=Numerics(i)
    i = i + 1
  Next
  ActiveDocument.FormFields("Result2").DropDown_
    .Value = 1

2. Please remove any code that is not really relevant.

3. You mention an error. When ever you get an error, it is very helpful for us to know what the error IS.

4. Use pusedo code.

OK. I am trying to understand what is happening.

I get the error when I change the selections of the dropdowns more than once and only if I change them to the same "selection" at one time and back. My real issue is getting all four sets of dropdowns working off of the same set of cases.

You have 6 formfields - lets call then DD1, DD2, DD3 ....

If I read it correctly, you have TWO conditional logic processes. You are picking up the Value results from Dropdown1, and Dropdown2.

Lets walk through some.

Case 3
  ActiveDocument.FormFields("Result").DropDown.ListEntries.Clear
  For var = 1 To 4
    ActiveDocument.FormFields("Result").DropDown.ListEntries.Add Name:=Description(i)
    i = i + 1
  Next
ActiveDocument.FormFields("Result").DropDown.Value = 1

This is from the first Select Case. In this example, IF the resul;t of DropDown1 = 3, clear Result dropdown, add the list from the Description array, and make the value of Result dropdown = 1.

Tell me what is wrong. You make a selection in one dropdown, and it repopulates the .list in another. Are you asking if you can make make the REPOPULATED dropdown have a specific item showing at the top?

If so, please state this, because that is NOT the purpose of the code. The code POPULATES the other dropdowns. It has NO logic to determine WHICH item is at the top. The list is populated directly from the order in the arrays. You can populate by extended logic. This can be done, but you will have to figure out the logic for it. You have no logic for this. If this is what you are trying to do, post back, and I will help you out.


Gerry
See my Paintings and Sculpture