Help with cascading dropdown list

markarius · #1 07-18-2020, 02:04 AM

Hi again Macropod and others,

Thanks for all the feedback. I managed to get things working. I do need help with something else.

Basically, I have parent content control "Spouse" and "A&A" as child.
if spouse is Yes, then A&A has multiple selection options, but of the different options, IF either "spouse is able and available" or "spouse is able and partially available" then I need that value outputted somewhere in the document. Currently, I'm doing that in the form of dropdown. I would prefer a neater solution... preferable as text output when one of those 2 is selected in A&A. Would appreciate if you can reply with the code needed for this. Please post complete code so that I can copy/paste and replace the entire thing.

below is existing code in it's entirety:

Code:

Option Explicit
Dim StrOption As String

Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)
With CCtrl
  Select Case .Title
    Case "Spouse", "A&A", "Minor": 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


' ///  SPOUSE - ABLE & AVAILABLE
  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 "Recipient is not married"
        StrOut = "N/A"
      Case Else
        .Type = wdContentControlText
        .Appearance = wdContentControlTags
        .Range.Text = ""
        .Type = wdContentControlDropdownList
        .Appearance = wdContentControlTags
    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
      .Appearance = wdContentControlTags
      .Range.Text = ""
      .Type = wdContentControlDropdownList
      .Appearance = wdContentControlTags
    End With
  End If



' ///  SPOUSE - ALTERNATE RESOURCE
  If .Title = "A&A" Then
    If StrOption = .Range.Text Then Exit Sub
    Select Case .Range.Text
      Case "Spouse is able and available"
        StrOut = "Spouse is able and available."
        
      Case "Spouse is able and partially available"
        StrOut = "Spouse is able and partially available."

      Case Else
        .Type = wdContentControlText
        .Appearance = wdContentControlTags
        .Range.Text = ""
        .Type = wdContentControlDropdownList
        .Appearance = wdContentControlTags
    End Select
    With ActiveDocument.SelectContentControlsByTitle("AltResource-Spouse")(1)
      .DropdownListEntries.Clear
      For i = 0 To UBound(Split(StrOut, ","))
        .DropdownListEntries.Add Split(StrOut, ",")(i)
      Next
      .Type = wdContentControlText
      .Appearance = wdContentControlTags
      .Range.Text = ""
      .Type = wdContentControlDropdownList
      .Appearance = wdContentControlTags
    End With
  End If


' ///   MINOR
  If .Title = "Minor" Then
    If StrOption = .Range.Text Then Exit Sub
    Select Case .Range.Text
      Case "Yes", "Recipient is not a minor"
        StrOut = "N/A"
      Case "Recipient is not a minor"
        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

markarius · #2 07-18-2020, 02:15 AM

Hi

I need help with a small issue in my code.
Basically, the code has parent dropbox "Lives with", and if user selects "recipient lives alone" then it currently limits child dropbox (Occupants) to "N/A"
but if user selects "Recipient shares home" I would like for it to output a value, but for that box to be a combo box and not a dropbox, meaning that user can edit the output values instead of locked field. I would appreciate all the help I can get on this. Thanks!

Quote:

' /// RECIPIENT LIVES WITH OTHERS - OTHER OCCUPANTS
' note: lives alone = N/A (ok), but lives with other needs to allow custom text but DB is locked.
' reminder: once code is ready add "Lives with" to Document_ContentControlOnEnter
If .Title = "Lives with" Then
If StrOption = .Range.Text Then Exit Sub
Select Case .Range.Text
Case "Recipient lives alone"
StrOut = "N/A"
Case "Recipient shares home"
StrOut = "Occupant1; Occupant2" ' << need this to be editable.
Case Else
.Type = wdContentControlText
.Range.Text = ""
.Type = wdContentControlComboBox
End Select
With ActiveDocument.SelectContentControlsByTitle("Occup ants")(1)
.DropdownListEntries.Clear
For i = 0 To UBound(Split(StrOut, ","))
.DropdownListEntries.Add Split(StrOut, ",")(i)
Next
.Type = wdContentControlText
.Range.Text = ""
.Type = wdContentControlDropdownList

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Dropdown list, Macro	shield5	Excel Programming	7	10-27-2013 01:51 AM
VBA: How to place dropdown list next to text	YigalB	Word VBA	0	08-11-2013 01:48 PM
block selection in dropdown list	Intruder	Excel	2	01-10-2013 10:20 AM
dropdown list for documents	r_p_t_0	Word	2	12-18-2012 05:55 AM
Dropdown list of email addresses	J Partridge	Outlook	1	01-13-2011 06:37 AM