Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-23-2017, 12:51 PM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default Populate Content Control Dropdowns from Excel

Hello.



I am a teacher who is in waaaay over her head. I'm hoping to find some help here! (Although I'd really love to find someone i can just pay to create what I need... is that allowed here?)

I am trying to create a lesson plan template in Word.

I'd like to choose a Learning Target from a drop-down list and, based on that selection, have the next drop-down list populate with objectives pulled from an excel workbook.

This must be doable, but I'm in over my head and the answers to similar questions on this forum leave me in the dust. Have tried finding videos on YouTube, but have not had any luck.

I've created the lesson plan format - I just need to link/connect the first drop-down to the next one and then connect that one to the excel pages.

Anyone?

Thank you,
Deirdre Kelly
Split from: https://www.msofficeforums.com/word-...drop-down.html

Last edited by macropod; 08-28-2017 at 06:42 PM. Reason: Split from http://www.msofficeforums.com/word-vba/16330-how-import-list-excel-into-drop-down.html
Reply With Quote
  #2  
Old 08-23-2017, 03:23 PM
macropod's Avatar
macropod macropod is offline Populate Content Control Dropdowns from Excel Windows 7 64bit Populate Content Control Dropdowns from Excel 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

Quote:
Originally Posted by Deirdre Kelly View Post
I'd like to choose a Learning Target from a drop-down list and, based on that selection, have the next drop-down list populate with objectives pulled from an excel workbook.

This must be doable, but I'm in over my head and the answers to similar questions on this forum leave me in the dust. Have tried finding videos on YouTube, but have not had any luck.

I've created the lesson plan format - I just need to link/connect the first drop-down to the next one and then connect that one to the excel pages.
The code in https://www.msofficeforums.com/word-...html#post46287 does that kind of thing. What it requires is an appropriately-designed workbook, with all the data for a given selection in the first dropdown to be on the same row. Of course, you'd also need to edit the code to reference the appropriate workbook and content controls.

Without seeing your workbook and the document you want to update, it's impossible to provide specific advice. You could attach both to a post (delete anything sensitive) via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 08-24-2017, 05:03 AM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default Attaching Work

Thank you.

Have attached documents.
Attached Files
File Type: docx Lesson Plan 2017-2018 MASTER B.docx (28.1 KB, 22 views)
File Type: xlsx Master List of I Can Statements.xlsx (20.8 KB, 31 views)
Reply With Quote
  #4  
Old 08-24-2017, 05:52 PM
macropod's Avatar
macropod macropod is offline Populate Content Control Dropdowns from Excel Windows 7 64bit Populate Content Control Dropdowns from Excel 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

Your Excel data aren't really laid out in a way that's conducive to what you want to achieve, but that could be overcome. It's also not apparent how all this is supposed to work in your Word document. All 5 rows under 'Learning Targets:' have the same dropdown options which, presumably, are meant to result in another set of choices in the adjacent column. So, if Knowledge is chosen for 'Learning Target 1', presumably the adjacent dropdown would be populated with Distinguished, Proficient, Apprentice, & Novice, all from the Knowledge worksheet. But it's by no means apparent what you want to do with the rest of the data on that worksheet.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 08-24-2017, 07:55 PM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default

Hi Paul.

You're correct.

I need to be able to choose from the drop-down list under Learning Targets (Knowledge, Questioning, Research, Problem Solving, Leadership, Goals, or Production) and, based on that selection, the adjacent field needs to be populated with the corresponding list in the excel workbook (let's call those "I Can Statements".

That same feature needs to be repeated five times - allowing me to choose (for example) Knowledge two times with two different "I Can Statements" from the Knowledge list in excel, one Leadership target with one "I Can Statement" from the Leadership list, and maybe 2 Problem Solving targets with two "I Can Statements" from the Problem Solving list.

Does that sound doable?

Based on a previous post, it sound like the excel lists need to be configured differently. Am I correct in remembering that you said to someone else that all the content needs to be on a single line? I can get on that if needed. With what symbol do I separate each item?

Will that allow the drop-down list to be organized vertically?

So appreciating your help. Thank you.

Truly - I'm more than willing to pay someone to create this - if that is of interest. Funds are limited, but this will be a big time saver for me and others.
Reply With Quote
  #6  
Old 08-24-2017, 11:46 PM
macropod's Avatar
macropod macropod is offline Populate Content Control Dropdowns from Excel Windows 7 64bit Populate Content Control Dropdowns from Excel 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

As I said previously, I've been able to re-work the code to suit your workbook structure. See attached. You can access the macro by opening the document and pressing Alt-F11. Once in the VBE, Alt-F11 again returns you to the document.

As coded, the Document_New macro in the document looks for the workbook in the user's 'Documents' folder. If you're keeping it somewhere else and/or decide to use a different workbook name, modify:
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Master List of I Can Statements.xlsx"
to suit. Just don't change the workbook structure (you can add more sheets with the same structure, if needed, or delete existing sheets).

The document also contains a Document_ContentControlOnExit macro, which does all the work when you choose an item from one of the "LearningTarget" or "LessonPlans" dropdowns. Exiting either of these controls will update the associated content.

As mentioned previously, the document contains a Document_New macro. That's because it's intended to be saved as a macro-enabled template:
File|SaveAs>Save As Type: Word Macro-Enabled Template (*.dotm)
You should do that once you've updated the StrWkBkNm variable (if needed) and you're happy with how it all works. In the meantime, running the Document_New macro (e.g. via Alt-F8) will populate/refresh the document with the Excel data (it actually has already had that part done). Once you've done saved the document as a template, double-clicking on that template will create a new document and populate it with the Excel data. You can save the new document in the docx format.
Attached Files
File Type: docm Lesson Plan MASTER.docm (56.3 KB, 36 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 08-25-2017, 08:41 AM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default

This is really getting there!!!! Wow! Thank you so much for your help.

Clearly, there are soooooo many more levels to Word that I understand currently.... need to take classes.

Questions:

~ When the Objective drop-down box opens, it shows the organizational structure of the objectives list (the subheadings of each list, like "Apprentice Intermediate"), rather than showing the organization as well as the whole list of I Can Statements.
----When I click on the Objectives drop-down list, I need to be able to see the organizational levels and all the I Can Statements so I can select one I Can Statement from the whole list.

~ Regardless of which selection is made in the LearningTargetBox, the ObjectivesBox is showing content from the "Knowledge" list.

~ Spelling mistake for the work "Intermediate"... I must have replicated it throughout. I've reattached the workbook without mistake.

~ There is something weird going on with LearningTargetBox3... when it accepts a selection it seems to lose its drop-down list after that?
Attached Files
File Type: xlsx Master List of I Can Statements.xlsx (21.2 KB, 16 views)
Reply With Quote
  #8  
Old 08-26-2017, 01:45 AM
macropod's Avatar
macropod macropod is offline Populate Content Control Dropdowns from Excel Windows 7 64bit Populate Content Control Dropdowns from Excel 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

Quote:
Originally Posted by Deirdre Kelly View Post
When the Objective drop-down box opens, it shows the organizational structure of the objectives list (the subheadings of each list, like "Apprentice Intermediate"), rather than showing the organization as well as the whole list of I Can Statements.
----When I click on the Objectives drop-down list, I need to be able to see the organizational levels and all the I Can Statements so I can select one I Can Statement from the whole list.
There is a limit to how much content can be displayed in a drop-down. Amongst other things, items can only span a single line. When you select an Objectives item and exit the dropdown, its I Can Statements are added to the cell. You can change the selection, if needs be, to generate a different set of I Can Statements - or you could even edit them (if appropriate).
Quote:
Originally Posted by Deirdre Kelly View Post
Regardless of which selection is made in the LearningTargetBox, the ObjectivesBox is showing content from the "Knowledge" list.
They might appear to be the same, but that's only because you have the same objectives on all worksheets.
Quote:
Originally Posted by Deirdre Kelly View Post
Spelling mistake for the work "Intermediate"... I must have replicated it throughout.
If you find an error or want to change an I Can Statement, for example, simply edit the workbook, re-run the Document_New macro, then re-do the relevant dropdown selection(s).
Quote:
Originally Posted by Deirdre Kelly View Post
There is something weird going on with LearningTargetBox3... when it accepts a selection it seems to lose its drop-down list after that?
That seems to be related to the cell's formatting being different from its predecessors and the fact the content control's 'cannot be deleted' property hadn't been checked. Fixed.

I've enhanced the code a bit and made the cell formatting more consistent for all items.

FWIW, I could also make the form work so that you couldn't select anything from LearningTargets 2-5 until you'd selected something from LearningTarget 1 and - with significantly more work - that the only items available for selection in LearningTargets 2-5 would be items that hadn't already been selected.
Attached Files
File Type: docm Lesson Plan MASTER.docm (57.9 KB, 20 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 08-26-2017, 11:11 AM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
There is a limit to how much content can be displayed in a drop-down. Amongst other things, items can only span a single line. When you select an Objectives item and exit the dropdown, its I Can Statements are added to the cell. You can change the selection, if needs be, to generate a different set of I Can Statements - or you could even edit them (if appropriate).
-- So... is there no way to get it to do what I need it to do? Select from 7 Domains, then select one objective from a list of 30-40 objectives?

-- I've attached a new spreadsheet.... with the progress levels removed.... placed at the ends of each objective.... hoping that might help us?

-- I like the idea that the objectives, once selected can be edited. That's helpful.
Quote:
Originally Posted by macropod View Post
Quote:
Originally Posted by Deirdre Kelly View Post
Regardless of which selection is made in the LearningTargetBox, the ObjectivesBox is showing content from the "Knowledge" list.
They might appear to be the same, but that's only because you have the same objectives on all worksheets.
-- I must be doing something wrong then - because when I choose Knowledge, the same objectives show up as when I select Leadership. The objectives on each page of the worksheet are different.
Quote:
Originally Posted by macropod View Post
If you find an error or want to change an I Can Statement, for example, simply edit the workbook, re-run the Document_New macro, then re-do the relevant dropdown selection(s).
-- Looks fixed. Thanks.
Quote:
Originally Posted by macropod View Post
That seems to be related to the cell's formatting being different from its predecessors and the fact the content control's 'cannot be deleted' property hadn't been checked. Fixed.
-- Thanks!
Quote:
Originally Posted by macropod View Post
I've enhanced the code a bit and made the cell formatting more consistent for all items.
-- Thanks!
Quote:
Originally Posted by macropod View Post
FWIW, I could also make the form work so that you couldn't select anything from LearningTargets 2-5 until you'd selected something from LearningTarget 1 and - with significantly more work - that the only items available for selection in LearningTargets 2-5 would be items that hadn't already been selected.
-- I like the idea of not being able to select from LT 2-5 until LT 1 is selected.
-- There can be a need to select the same LearningTarget repeatedly - one lesson might have 2-3 objectives from the Knowledge list. (We would not select the same objective repeatedly.)
Attached Files
File Type: xlsx Master List of I Can Statements 2.xlsx (20.7 KB, 22 views)
Reply With Quote
  #10  
Old 08-27-2017, 12:27 AM
macropod's Avatar
macropod macropod is offline Populate Content Control Dropdowns from Excel Windows 7 64bit Populate Content Control Dropdowns from Excel 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

I now see what you meant by
Quote:
Originally Posted by Deirdre Kelly View Post
Regardless of which selection is made in the LearningTargetBox, the ObjectivesBox is showing content from the "Knowledge" list.
Fixed.

I've also added the code to prevent LearningTargets 2-5 until something is selected from the previous LearningTarget.

The attached template contains none of the workbook data - which is how you'd normally store such a template. Double-clicking on it should create a new document with the data.

I have retained the code that works with your original workbook structure; I see no advantage in changing it. That said, I'm not sure what you mean by:
Quote:
Originally Posted by Deirdre Kelly View Post
-- So... is there no way to get it to do what I need it to do? Select from 7 Domains, then select one objective from a list of 30-40 objectives?
Do you mean that, if you choose Knowledge, the choices that become available are all of the entries on that sheet? If so, you could only choose one of those 30-40 entries, whereas the current process allows you to output all that are applicable to Distinguished, for example, and gives you the ability to delete any you don't want and/or to re-word the ones that are there.
Attached Files
File Type: dotm Lesson Plan MASTER.dotm (51.6 KB, 16 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 08-27-2017, 05:48 PM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default

Hi Paul.

FYI: I didn't see anything attached this time.

I have retained the code that works with your original workbook structure; I see no advantage in changing it.

-- I think the advantage of the data in the second workbook is that the current method requires me to know which I Can Statements fall under each progress title (Intermediate Apprentice, etc.) in order to land on the right I Can Statement. I need to see all the I Can Statements in order to choose the one that best fits the lesson. I also like the fact that the progress titles are available at the end of each I Can Statement as that is information my administration likes to see.

Do you mean that, if you choose Knowledge, the choices that become available are all of the entries on that sheet? If so, you could only choose one of those 30-40 entries, whereas the current process allows you to output all that are applicable to Distinguished, for example, and gives you the ability to delete any you don't want and/or to re-word the ones that are there.

-- Yes!!!! That's it!!!! "When selected, all the entries on that sheet." That's it! I'm sorry - I didn't know how to word it like that to at the start.
That's the preferred method - choose the Domain (Knowledge, Research, etc.) and then choose only one of those 30-40 entries. If I need two I Can Statements from Research, I can choose Research in LearningTarget1 Box and LearningTarget2 Box.

That would be what's needed.
:0)
Reply With Quote
  #12  
Old 08-27-2017, 09:16 PM
macropod's Avatar
macropod macropod is offline Populate Content Control Dropdowns from Excel Windows 7 64bit Populate Content Control Dropdowns from Excel 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 updated template attached.
Attached Files
File Type: dotm Lesson Plan MASTER.dotm (50.2 KB, 31 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 08-28-2017, 07:21 PM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default

I downloaded it, but there is no content in the drop downs.

Am I doing something wrong?
Reply With Quote
  #14  
Old 08-28-2017, 07:26 PM
macropod's Avatar
macropod macropod is offline Populate Content Control Dropdowns from Excel Windows 7 64bit Populate Content Control Dropdowns from Excel 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

As I said previously, it's a template. Double-clicking on it should result in a new document being created and the data from the Excel workbook being imported into the document (not the template) - assuming the workbook is in your 'Documents' folder.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 08-28-2017, 07:31 PM
Deirdre Kelly Deirdre Kelly is offline Populate Content Control Dropdowns from Excel Windows 10 Populate Content Control Dropdowns from Excel Office 2013
Novice
Populate Content Control Dropdowns from Excel
 
Join Date: Aug 2017
Posts: 12
Deirdre Kelly is on a distinguished road
Default

I think I understand... but when I open the document - in the same location as the rest of the documents we've been working with - it gives me an error message that it can't find the workbook.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel formula while using dropdowns rjf1127 Excel 12 01-25-2017 10:53 AM
Populate Content Control Dropdowns from Excel How to populate dependent dropdowns and auto-fill text fields simultaneously? vera Word VBA 1 10-07-2016 07:41 PM
Populate Drop-Down Content Control from CustomXMLPart added to the Document KhmerBoi1 Word VBA 0 05-11-2016 01:10 PM
Have Text Entered in Content Control / PreDefined Field Populate Field in Footer bzowk Word 1 04-14-2016 11:50 AM
Populate Content Control Dropdowns from Excel Deleting a table from a content control -- preserving the content control BrainSlugs83 Word Tables 8 11-14-2013 03:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:40 AM.


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