Content Controls - Dependent Dropdown & Text

[Mightymegs]

Hi Macropod,



Not sure where to put this one as there are many threads with similar queries.

I have been creating a timetable template for a local college using CCs. When a subject is selected in the first dropdown the options are given to choose a lesson number in the second dropdown and a text box also autopopulates with the tutor's name.

I am a complete newbie to VBA and have found your various posts helping others with similar problems very useful in creating my document (Thank you!).

My document is functional, however I have 2 queries I hope you can help me with;

1) There are occasionally scenarios where the subject, no and tutor boxes may need to have a different 'one off' entry, e.g. when there is a field trip somewhere instead of a subject or when a tutor is away and someone else stands in. Is it possible to use combo boxes/have some kind of text override built in to allow this in all 3 boxes? (I am hoping to avoid leaving the boxes as deletable)
2) I need to create further CC boxes for all rows where it says '1st Year' or '2nd Year' and create identical code for each (all 1st year has same options and all 2nd year has same options). Is there any way to avoid repetitive code so that if I need to make changes, for example change the tutor of a subject or add a new subject, I only need to do it once?

I have attached my document for you to see and hope my explanation is not too confusing!

Many Thanks,

Megan

[macropod]

Try the attached. You'll note that I've greatly simplified the code. Tutor details are now gleaned directly from the dropdown values and I've added a sub to facilitate editing those.

[Mightymegs]

Hi Macropod,
Thanks for taking a look at the document. I like that there is now only one place to edit the lesson info, it may be easier if the tutor info is edited from there too. Is the added sub so I only need to change the tutor info in one properties box for them all to change? Unfortunately when testing out the code by selecting the subject, the following error message is displayed 'Runtime error 6189: This property can only be used with dropdown list or combo box content controls' when selecting 'debug' the following code is highlighted '.DropdownListEntries.Clear'. Word is also crashing on me so it's difficult to explore what's happening, could you check on the code? I'm using office 2010 if that makes a difference.
Thanks
Megan

[macropod]

Updated. Try it now.

Is the added sub so I only need to change the tutor info in one properties box for them all to change?

That is quite possible, but I was loath to assume every reference to a tutor should be changed. The current approach requires each subject & session run by that tutor to at least be considered, which is a safeguard against both: (a) updating all instances when only a few need updating; and (b) overlooking some if they were to be done manually (which is still possible). For code to update all instances, you might use:

Sub BulkUpdateTutor()
Dim i As Long, j As Long, OldTutor As String, NewTutor As String
OldTutor = Trim(InputBox("Old Tutor"))
NewTutor = Trim(InputBox("New Tutor", , OldTutor))
If OldTutor = "" Then Exit Sub
With ActiveDocument
  For i = 1 To .SelectContentControlsByTitle("Subject").Count
    With .SelectContentControlsByTag("Subject_" & i)(1)
      For j = 1 To .DropdownListEntries.Count
        With .DropdownListEntries(j)
          If Trim(.Value) = OldTutor Then
            .Value = Replace(.Value, OldTutor, NewTutor)
          End If
        End With
      Next
    End With
  Next
End With
End Sub

[Mightymegs]

Hi Macropod,

I hope you are well. Sorry for the long delay, the document is working great, thank you for your input.

There was one other adaptation I would like to make if possible but I am struggling to find a solution.

Is possible to use a combo box instead of a dropdown as the 'subject' box? And how would I code for that instead (do I just change the name 'DropdownList' to something else)?

The reason being, there will be one off field trips where the subject is not run in that time session. So for example, if there were to be a morning trip to a museum for Year 1 students, I would like to be able to type 'museum trip' in the subject box instead of selecting a predetermined subject.

Thanks

Megan

[Mightymegs]

Hi Macropod,

I hope you are well. Sorry for the long delay, the document is working great, thank you for your input.

There was one other adaptation I would like to make if possible but I am struggling to find a solution.

Is possible to use a combo box instead of a dropdown as the 'subject' box? And how would I code for that instead (do I just change the name 'DropdownList' to something else)?

The reason being, there will be one off field trips where the subject is not run in that time session. So for example, if there were to be a morning trip to a museum for Year 1 students, I would like to be able to type 'museum trip' in the subject box instead of selecting a predetermined subject.

Thanks

Megan

[macropod]

You can already add another entry to the subject list, simply by clicking on the content control, then choosing Developer>Controls>Properties>Add. Having to do it that way ensures greater consistency in the data entry. Otherwise:

Sub Modify()
Dim CCtrl As ContentControl
For Each CCtrl In ActiveDocument.ContentControls
  With CCtrl
    If .Type = wdContentControlDropdownList Then .Type = wdContentControlComboBox
  End With
Next
End Sub

You will also need to edit the existing VBA code to change the wdContentControlDropdownList references to wdContentControlComboBox.