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

Jeff_Reach · 04-07-2022, 09:50 PM

Would it be possible to utilize an OR statement if each value in a dependent drop down is the same?

In my project I need to have multiple combo-boxes that utilize identical information. Although I could expand the code that has been provided in this thread (and will if I need to) I think that the more I copy and modify the more likely it is that I'll break the code. To help characterize what I'm talking about, I've modified another users code to reflect two different Spouse cases with the same StrOut variables. As you can imagine, if I'm copying / modifying 40 of these "Spouse" cases that code would be both long and probably broken.

I realize I'm already pushing my luck here today as Macropod helped me a lot earlier, so I completely understand if you all don't have time for another question. However, if you do and wouldn't mind explaining why an OR statement can't be used or how to put it in I'd very appreciative. Currently my biggest "what do I do now" concern is that even if I put an OR clause at the top of the code, I have no idea how to tell Word that the resulting drop-down should go to a specific content control.

Code:

With CCtrl
     If .Title = "Spouse1" OR "Spouse2" Then
            If StrOption....

____

Code:

Option Explicit
Dim StrOption As String

Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)
With CCtrl
  Select Case .Title
    Case "Spouse1", "Spouse2": 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
  If .Title = "Spouse1" 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
  If .Title = "Spouse2" 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