Microsoft Office Forums - View Single Post - [Solved] Help with cascading dropdown list

Jeff_Reach · 04-08-2022, 09:24 AM

Hi Macropod,

If I'm doing it right, your most recent code works well for the first dependent drop down box but when I add in other dependent content controls they aren't registering. I think it's highly like that it's user error, but if it's not I've listed my assumption of what's happening below.

My assumption is that I'm not telling VBA which content controls should be dependent on the value of another content control beyond the first instance. To fix that I tried directing the new code by adding the following section to your code and also simply adding the additional subchapters to the first section (i.e. "R1SubChapter", "R2SubChapter"). Unfortunately in both instance I failed to properly tell VBA that R1Chapter's inputs should go with R1SubChapter's content control, while R2Chapter's inputs should go with R2SubChapter's content control. At this point I'm thinking about tying all of the chapters/subchapters into a variable so that when the time comes there is only one spot to update the reference information. That being said this is only a guess, and I'm not entirely sure that this is even possible.

Code:

    With ActiveDocument.SelectContentControlsByTitle("R1SubChapter")(1)
        .DropdownListEntries.Clear
        .Type = wdContentControlText
        .Range.Text = ""
        .Type = wdContentControlDropdownList
        For i = 0 To UBound(Split(StrOut, "|"))
          .DropdownListEntries.Add Split(StrOut, "|")(i)
        Next
      End With

    With ActiveDocument.SelectContentControlsByTitle("R2SubChapter")(1)
        .DropdownListEntries.Clear
        .Type = wdContentControlText
        .Range.Text = ""
        .Type = wdContentControlDropdownList
        For i = 0 To UBound(Split(StrOut, "|"))
          .DropdownListEntries.Add Split(StrOut, "|")(i)
        Next
      End With

I acknowledge that I'm probably not explaining the intent well, but what I'm trying to do is create multiple dependent drop down lists between two different content controls. All of these drop downs will utilize the same Cases (i.e. Chapter 1) and StrOut values (i.e. Subchapter 1, Subchapter 2).

I'm not sure if you've ever come across this type of situation but the problem we're trying to solve is that users will be asked to cite a chapter and a subchapter that supports why an action is necessary or unnecessary. As each request for that determination utilizes the same chapters/subchapters I'm hopeful that the long list of Chapters / Subchapters would only need to be entered once. Hopefully with this additional context I've at least made it a bit more clear of what my intentions are. In short though my question my primary questions is, How can I modify the below code so that the a series of content controls can utilize the same chapters and subchapters without having to have 20 identical chapters/subchapters listed? For example, a user selects drop down list [A] and gets options "1,2,3,4". After selecting 1, a dependent drop-down list [a] displays options "x,y,z". That same user can then select drop down list [B] and also gets options "1,2,3,4." After selecting 1 again, dependent drop-down list [b] also displays options "x,y,z."

I'm extremely new at this, and have been out of my depth for some time now. So I apologize to anyone reading this who found anything in this post absurd, confusing, or ignorant. Nevertheless, I truly appreciate everything Macropod and others have done for me and the community. Thank you all very much for your time and patience.

Also as a better reference to what the code looks like I've included a modified version below:

Code:

  Option Explicit
  Dim StrOption As String
   
  Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)
       With CCtrl
            Select Case .Title
                 Case "R1Chapter", "R2Chapter", "R3Chapter": StrOption = .Range.Text
            End Select
       End With
  End Sub
   
  Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
  Application.ScreenUpdating = False
  Dim i As Long, StrOut As String
  With CCtrl
    Select Case .Title
       Case "R1Chapter", "R2Chapter", "R3Chapter"
          With CCtrl
            Select Case .Range.Text
                Case StrOption: Exit Sub
                Case "Chapter 1"
                      StrOut = "SubChapter1| SubChapter2| SubChapter3|SubChapter4"
                Case "Chapter 2"
                      StrOut = "SubChapterA| SubChapterB| SubChapterC|SubChapterD"
            End Select
       End With
        With ActiveDocument.SelectContentControlsByTitle("R1SubChapter", 
“R2SubChapter”, “R3Subchapter”)(1)
          .DropdownListEntries.Clear
          .Type = wdContentControlText
          .Range.Text = ""
          .Type = wdContentControlDropdownList
          For i = 0 To UBound(Split(StrOut, "|"))
            .DropdownListEntries.Add Split(StrOut, "|")(i)
          Next
        End With
    End Select
  End With
  Application.ScreenUpdating = True
  End Sub