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]