View Single Post
 
Old 08-24-2017, 11:46 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,342
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, 39 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote