Help with cascading dropdown list

macropod · #1 05-11-2020, 03:48 PM

Quote:

Originally Posted by markarius

So if I'm adding more drop downs, then am I correct in assuming that I can just duplicate the following code, put it on the bottom of existing code and modify values to correspond to new content controls

Basically, yes. You might also want to add a corresponding reference to the additional content control in Document_ContentControlOnEnter macro also.

Quote:

Originally Posted by markarius

how can I set it so that the code either leaves this setting alone to whatever was original assigned to the drop down, or, if not possible, then specify that it should be a start/end tag"?

The code is already written to only reset/update a dependent dropdown if it's parent is reset/updated.

markarius · #2 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 · #3 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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