Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-27-2020, 11:46 AM
Mightymegs Mightymegs is offline Content Controls - Dependent Dropdown & Text Windows 10 Content Controls - Dependent Dropdown & Text Office 2010
Novice
Content Controls - Dependent Dropdown & Text
 
Join Date: Feb 2020
Posts: 4
Mightymegs is on a distinguished road
Default Content Controls - Dependent Dropdown & Text

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
Attached Files
File Type: docm Timetable_Working_Copy(2).docm (112.3 KB, 16 views)

Last edited by macropod; 02-28-2020 at 03:57 AM. Reason: Split from: https://www.msofficeforums.com/word-vba/38659-content-controls-dependent-dropdown.html
Reply With Quote
  #2  
Old 02-28-2020, 06:24 AM
macropod's Avatar
macropod macropod is offline Content Controls - Dependent Dropdown & Text Windows 7 64bit Content Controls - Dependent Dropdown & Text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
Attached Files
File Type: docm Timetable_Working_Copy.docm (108.1 KB, 33 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 03-01-2020, 09:45 AM
Mightymegs Mightymegs is offline Content Controls - Dependent Dropdown & Text Windows 10 Content Controls - Dependent Dropdown & Text Office 2010
Novice
Content Controls - Dependent Dropdown & Text
 
Join Date: Feb 2020
Posts: 4
Mightymegs is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 03-01-2020, 02:41 PM
macropod's Avatar
macropod macropod is offline Content Controls - Dependent Dropdown & Text Windows 7 64bit Content Controls - Dependent Dropdown & Text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Updated. Try it now.
Quote:
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:
Code:
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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-18-2020, 04:55 AM
Mightymegs Mightymegs is offline Content Controls - Dependent Dropdown & Text Windows 10 Content Controls - Dependent Dropdown & Text Office 2010
Novice
Content Controls - Dependent Dropdown & Text
 
Join Date: Feb 2020
Posts: 4
Mightymegs is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 05-18-2020, 04:56 AM
Mightymegs Mightymegs is offline Content Controls - Dependent Dropdown & Text Windows 10 Content Controls - Dependent Dropdown & Text Office 2010
Novice
Content Controls - Dependent Dropdown & Text
 
Join Date: Feb 2020
Posts: 4
Mightymegs is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 05-18-2020, 05:34 AM
macropod's Avatar
macropod macropod is offline Content Controls - Dependent Dropdown & Text Windows 7 64bit Content Controls - Dependent Dropdown & Text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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:
Code:
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Content Controls - Dependent Dropdown & Text Content Controls - Dependent Dropdown someazguy Word VBA 14 02-05-2023 08:01 PM
Content Controls - Dependent Dropdown & Text Multiple dependent dropdown lists in table with add new row option jeweldarby Word VBA 6 05-21-2022 04:37 PM
Content Controls - Dependent Dropdown & Text Dependent dropdown and repeat section NLJ Word VBA 3 01-12-2020 05:00 PM
Content Controls - Dependent Dropdown & Text Dropdown Menu Dependent on a Previous Drop Down lgsikaffy Word VBA 3 12-14-2018 05:14 PM
Dropdown dependent text JakeLRL Word VBA 7 04-07-2016 08:26 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:43 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft