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

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]

Thank you.

Have attached documents.

Attached Files

	Lesson Plan 2017-2018 MASTER B.docx (28.1 KB, 25 views)
	Master List of I Can Statements.xlsx (20.8 KB, 35 views)

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]

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.

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

Lesson Plan MASTER.docm (56.3 KB, 39 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

Master List of I Can Statements.xlsx (21.2 KB, 18 views)

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).
They might appear to be the same, but that's only because you have the same objectives on all worksheets.
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).
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

Lesson Plan MASTER.docm (57.9 KB, 22 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

-- 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.
-- 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.
-- Looks fixed. Thanks.
-- Thanks!
-- Thanks!
-- 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

Master List of I Can Statements 2.xlsx (20.7 KB, 24 views)

I now see what you meant byFixed.

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

Lesson Plan MASTER.dotm (51.6 KB, 18 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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)

Try the updated template attached.

Attached Files

Lesson Plan MASTER.dotm (50.2 KB, 33 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

Am I doing something wrong?

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]

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.