Help with cascading dropdown list

keianrad · #16 11-28-2018, 03:01 AM

Hello,

I have a form with 4 dropdown list, that 3 of the dependent dropdown list show same options like that:

Degree :(Bachelor, Master, PhD)
Priority Major 1: (Major1, Major2, Major3)
Priority Major 2: (Major1, Major2, Major3)
Priority Major 3: (Major1, Major2, Major3)

How can show an error when a same item selected.

Thank you for your help.

macropod · #17 11-28-2018, 03:29 AM

It's not apparent from your post what the dropdowns are or how they are related. That said, see the attachments to post #12 & #15.

gmaxey · #18 11-28-2018, 04:08 AM

Paul,

Your attachment to post 15 does not have a "Selections" content control. Did you attach the correct file?

gmaxey · #19 11-28-2018, 05:00 AM

It's a kluge, but you can create a pseudo multi-select CC using the OnEnter event and a userform. See attached.

macropod · #20 11-28-2018, 03:16 PM

Quote:

Originally Posted by gmaxey

Your attachment to post 15 does not have a "Selections" content control.

The "Selections" content control is in cell D2.

gmaxey · #21 11-28-2018, 05:44 PM

Paul,

Sorry. I see it now. thanks.

venkath · #22 07-01-2019, 04:36 AM

Hi

I need help on Help with cascading dropdown list
Content controls - Dependent dropdown.docm is usefull ,but how do i get the source code please?

Many Thanks
Venkath

macropod · #23 07-01-2019, 04:41 AM

See post #2.

venkath · #24 07-01-2019, 05:02 AM

Hi Paul,

I am in need of this only. But I couldnot able to access source code for this .
Please share the source code in a notepad .I am a beginner.

Please help
Hima.

macropod · #25 07-01-2019, 05:20 AM

The code is in the document. Download it and follow the instructions in post #2. Without both the code and the document, the code lacks context.

venkath · #26 07-01-2019, 07:19 AM

Thanks for the help Paul.It Worked.

markarius · #27 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

macropod · #28 05-10-2020, 12:57 AM

You cannot simply duplicate the code as you have done. For example, you can have only one instance of:

Code:

Option Explicit
Dim StrOption As String

and, likewise, there can only be one Sub of a given name. The correct way of addressing the latter is by combining the Subs. Thus:

Code:

Option Explicit
Dim StrOption As String

Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)
With CCtrl
  Select Case .Title
    Case "Spouse", "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
  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
  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 "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 · #29 05-11-2020, 10:30 AM

Thank you for that clarification.
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

EXISTING CODE:

Code:

Option Explicit
Dim StrOption As String

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

  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 "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

3rd Content drop down

Code:

  If .Title = "3rd dropddown name" Then
    If StrOption = .Range.Text Then Exit Sub
    Select Case .Range.Text
      Case "Yes", "blah....."
        StrOut = "N/A"
      Case "No"
        StrOut = "answer if no"
      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

I noticed that the code resets and forces the child drop down to show as "bounding box" instead of "start/end tag."
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"?

macropod · #30 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to 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