Muliple entries in Multiple dropdown lists

Haygordon · #1 03-31-2019, 08:10 AM

I am a total beginner on VBA and at 77 I really can't get my head round the solution in https://www.msofficeforums.com/word-...html#post46903!

Macropod's file "Content Controls - Dropdown Dependent Text.docm" does exact what I want to do for options in a letter document using the drop down "Value" text and inserting the selected text into another part of the document.

Whilst I can edit the original control and make it work in my document, for the life of me I cannot replicate the two drop down boxes to provide further option choices. I will need up to 6 pairs of boxes.

Are the boxes used in word 2013 Drop Down list controls or legacy controls and how are they linked together?

Is there such a thing as an idiot sheet showing how it is done?

gmaxey · #2 03-31-2019, 08:40 AM

I'm assuming you are referring to Paul's file posted back at the start of that thread.

There Paul uses a content control titled "Client" and another titled "ClientDetails." However, as the document contains only two CCs, he didn't use the title of the second in his code.

The CC used for the list of clients is a Content Control dropdown list. The one used to display the associated details can be a plain or rich text control.

The code goes in the ThisDocument module of the VB Project and can be modified as follows to add additional pairs:

Code:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long, strDetails As String
  With CC
    Select Case .Title
      Case "Client"
        For lngIndex = 1 To .DropdownListEntries.Count
          If .DropdownListEntries(lngIndex).Text = .Range.Text Then
            strDetails = Replace(.DropdownListEntries(lngIndex).Value, "|", Chr(11))
            Exit For
          End If
        Next
        ActiveDocument.SelectContentControlsByTitle("ClientDetails").Item(1).Range.Text = strDetails
     Case "Dogs"
        For lngIndex = 1 To .DropdownListEntries.Count
          If .DropdownListEntries(lngIndex).Text = .Range.Text Then
            strDetails = Replace(.DropdownListEntries(lngIndex).Value, "|", Chr(11))
            Exit For
          End If
        Next
        ActiveDocument.SelectContentControlsByTitle("DogDetails").Item(1).Range.Text = strDetails
     End Select
  End With
End Sub

So lets assume you want to add a pair that deals with dogs and their characteristics.

Insert a dropdown CC and title it "Dogs" as you add the type of dogs to the list, put the breed in the Display as Field and the characteristics in the Value fields each separated using "|"

Eg:

Display As Value
Beagle Friendly|Loyal|Playful
Pug Ugly|flat faced

Insert the plain text CC and title is DogDetails.

Haygordon · #3 04-02-2019, 09:44 AM

Thank you Greg for your guidance I am still struggling and have attached the letter I am trying to construct using the code shown below. There are 5 drop down list box controls in the English text and 5 rich text controls in the French text.

The VB code used is shown below:

Quote:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long, strDetails As String
With CC
Select Case .Title
Case "condition"
For lngIndex = 1 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
strDetails = Replace(.DropdownListEntries(lngIndex).Value, "|", Chr(11))
Exit For
End If
Next
ActiveDocument.SelectContentControlsByTitle("condi tionFR").Item(1).Range.Text = strDetails
Case "condition"
For lngIndex = 1 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
strDetails = Replace(.DropdownListEntries(lngIndex).Value, "|", Chr(11))
Exit For
End If
Next
ActiveDocument.SelectContentControlsByTitle("locat ion").Item(1).Range.Text = strDetails
Next
ActiveDocument.SelectContentControlsByTitle("sewer age").Item(1).Range.Text = strDetails
Next
ActiveDocument.SelectContentControlsByTitle("servi ces").Item(1).Range.Text = strDetailsservices
Next
ActiveDocument.SelectContentControlsByTitle("comms ").Item(1).Range.Text = strDetails
End Select
End With
End Sub

Any suggestions as to where I am going wrong? I am thinking that the drop down list control needs to be linked to the rich text control but I haven't got a clue where to start!

Thank you again for your help and patience.

Gordon Hayward

gmaxey · #4 04-03-2019, 08:13 AM

Gordon,

The document you attached didn't have code looking anything like that. If you are simply replacing an English value with a French value use:

Code:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
  With CC
    Select Case .Title
      Case "condition"
        For lngIndex = 1 To .DropdownListEntries.Count
          If .DropdownListEntries(lngIndex).Text = .Range.Text Then
            ActiveDocument.SelectContentControlsByTitle("conditionFR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
            Exit For
          End If
        Next lngIndex
      'Case and so on.
    End Select
  End With
End Sub

Haygordon · #5 04-03-2019, 11:28 AM

Thanks for the code advice - it works well on the first drop down box but comes up with errors when I add the remaining 4 options.
errors appearing are : End Select without End Case
Select Case without End Select

It seems to be going in circles.
Help!!!

Gordon

The amended code is shown below:

Quote:

Option Explicit
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
With CC
Select Case .Title
Case "condition"
For lngIndex = 1 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
ActiveDocument.SelectContentControlsByTitle("condi tionFR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
Exit For
End If
Next lngIndex
With CC
Select Case .Title
Case "location"
For lngIndex = 1 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
ActiveDocument.SelectContentControlsByTitle("locat ionFR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
Exit For
End If
Next lngIndex
With CC
Select Case .Title
Case "sewerage"
For lngIndex = 1 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
ActiveDocument.SelectContentControlsByTitle("sewer ageFR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
Exit For
End If
Next lngIndex
With CC
Select Case .Title
Case "services"
For lngIndex = 1 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
ActiveDocument.SelectContentControlsByTitle("servi cesFR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
Exit For
End If
Next lngIndex
With CC
Select Case .Title
Case "comms"
For lngIndex = 1 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
ActiveDocument.SelectContentControlsByTitle("comms FR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value

End Select
Exit For
End If
Next lngIndex
End Sub

gmaxey · #6 04-03-2019, 01:05 PM

You are making this much more difficult than in needs to b Gordon.

Code:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
  With CC
    Select Case .Title
      Case "condition"
        For lngIndex = 1 To .DropdownListEntries.Count
          If .DropdownListEntries(lngIndex).Text = .Range.Text Then
            ActiveDocument.SelectContentControlsByTitle("conditionFR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
            Exit For
          End If
        Next lngIndex
      Case "location"
        For lngIndex = 1 To .DropdownListEntries.Count
          If .DropdownListEntries(lngIndex).Text = .Range.Text Then
            ActiveDocument.SelectContentControlsByTitle("locationFR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
            Exit For
          End If
        Next lngIndex
      Case "The next one"
    End Select
  End With
End Sub

Haygordon · #7 04-04-2019, 05:32 AM

I bow before a man with far superior logic and coding skills that I will ever possess and must say a huge than you for all his skill and patience with someone who knows what
he needs to do but lacks the training to do it. With this code I can update a book I wrote in 2006 to a PC based series of 200+ letters for English speakers living in France.

Any chance of anyone organizing a VBA training course for the over 75's!

Thanks again,

Gordon Hayward

(well it would be a big grin if all the teeth were there....)

gmaxey · #8 04-04-2019, 06:28 AM

Gordon,

It just takes practice (lots of it).

If you are talking about many CCs (rather than just five), you can simplify that code greatly:

Code:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
  With CC
    Select Case .Title
      Case "condition", "location", "sewerage", "services", "comms"
        FillFrenchCC CC
    End Select
  End With
End Sub

Sub FillFrenchCC(oCC As ContentControl)
Dim lngIndex As Long
  With oCC
    For lngIndex = 1 To .DropdownListEntries.Count
      If .DropdownListEntries(lngIndex).Text = .Range.Text Then
        ActiveDocument.SelectContentControlsByTitle(oCC.Title & "FR").Item(1).Range.Text = .DropdownListEntries(lngIndex).Value
        Exit For
      End If
    Next lngIndex
  End With
End Sub

Hint: If all CCs in the document are paired with a French couterpart then the code could be simplified even further.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiple entries in dropdown lists	paul_pearson	Word VBA	151	10-18-2023 04:23 PM
Help with Dropdown Lists	Gerardo G	Word VBA	1	06-18-2015 11:10 PM
Dropdown Lists in Excel 2010	janehoss	Excel	0	12-01-2014 07:16 AM
VBA Dropdown change list Entries automatically	QA_Compliance_Advisor	Word VBA	20	09-16-2014 07:29 AM
Word Forms : Dropdown lists	wferaera45	Word	0	04-06-2006 03:02 AM