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