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

markarius · 05-09-2020, 10:19 PM

Hi Macropod,

I am working with a document that will have a ton of dependant content control drop down boxes. I followed your example and successfully created my first one. But when I duplicated the code and changed the values of the master and slave fields, I got a error.

see compile error:
Dropbox - compile error.png - Simplify your life

below is my VBA code, what am I doing wrong?

Code:

Option Explicit
Dim StrOption As String
Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)
If CCtrl.Title = "Spouse" 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 = "Spouse" Then
    If StrOption = .Range.Text Then Exit Sub
    Select Case .Range.Text
      Case "Yes"
        StrOut = "Spouse is able and available,Spouse is able and partially available,Spouse is able but not available,Spouse is available but not able,Spouse is IHSS Recipient,Other"
      Case "No"
        StrOut = "N/A"
      Case Else
        .Type = wdContentControlText
        .Range.Text = ""
        .Type = wdContentControlDropdownList
    End Select
    With ActiveDocument.SelectContentControlsByTitle("A&A")(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



Option Explicit
Dim StrOption As String
Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)
If CCtrl.Title = "Minor" 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 = "Minor" Then
    If StrOption = .Range.Text Then Exit Sub
    Select Case .Range.Text
      Case "Recipient is not a minor"
        StrOut = "N/A"
      Case "Yes"
        StrOut = "N/A"
      Case "No"
        StrOut = "Parent is prevented from full-time employment due to child's needs, Recipient is under the care of a legal guardian"
      Case Else
        .Type = wdContentControlText
        .Range.Text = ""
        .Type = wdContentControlDropdownList
    End Select
    With ActiveDocument.SelectContentControlsByTitle("MinorExp")(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