Microsoft Office Forums - View Single Post - [Solved] Dependent Dropdown Argument Is Way Too Long [Word 365

Jeff_Reach · 04-07-2022, 02:06 PM

All,

I'm currently trying to utilize the below formula attained from the referenced discussion to create a dependent drop down that contains all of the possible sub-chapter values. Unfortunately there are way to many sub-chapter values to fit on a single line for several of the chapters. How I know this is that VBA comes up with a pop-up saying that the the line of code is "too long."

To shorten the line, and also help make the code readable I have attempted using combinations of &_ and _ to no avail. While I've seen on several sites that I should break up the line of text into separate variables? or arrays? I have repeatedly failed to make this work without breaking Macropod's code. Therefore, I'm hopeful that someone out there can please tell me how to "shrink" or better organize the excessively long line of StrOut values.

Note: I've modified the chapter and sub-chapter names below to protect the specific purpose of the document. Meaning that in the actual code there isn't a [...] only a lot more subchapters.

Note 2: I think this question is sufficiently different than the original discussion. Therefore instead of tying my questions directly to the other discussion as a response I thought it would be best to create a new discussion topic.

Reference: https://www.msofficeforums.com/word/...wn-option.html

Code:

Option Explicit
Dim StrOption As String

Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)
If CCtrl.Title = "R1Chapter" Then StrOption = CCtrl.Range.Text
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
  If .Title = "R1Chapter" Then
      If StrOption = .Range.Text Then Exit Sub
    Select Case .Range.Text
      Case "Chapter 1"
        StrOut = "Subchapter 1|Subchapter 2|Subchapter 3....
      Case Else
        .Type = wdContentControlText
        .Range.Text = ""
        .Type = wdContentControlDropdownList
    End Select
    With ActiveDocument.SelectContentControlsByTitle("R1Subchapter")(1)
      .DropdownListEntries.Clear
      For i = 0 To UBound(Split(StrOut, "|"))
        .DropdownListEntries.Add Split(StrOut, "|")(i)
      Next
      .Type = wdContentControlText
      .Range.Text = ""
      .Type = wdContentControlDropdownList
    End With

  End If
End With
Application.ScreenUpdating = True
End Sub