Microsoft Excel Webinar

Results 1 to 4 of 4

Thread: Adding VBA macros to template

  1. #1

    Adding VBA macros to template

    I am using Word 2010. I have created a template that contains bookmarks and cross references. I created one macro to update all fields at the same time after filling certain sections in. In addition, I have another macro that changes the table/cell background and font color based on some input criteria. To make these macros easier to execute I created two quick access toolbar shortcuts. Works great on my machine but when I send the template to someone else to use there is no macro code embedded nor do they have the quick access toolbar icons I created that were assigned to the macros.

    Can anyone offer a solution so that the template retains the macros and short cuts I created so that they can be available to users on their pc's?

    I appreciate any assistance.

    Regards,

    Mark

  2. #2
    There are a number of things going on there.

    1) Where are the two macros you created? Just because you created a template with bookmarks and xrefs doesn't mean you stored the macro in it. Is the template a .dotx or a .dotm? in Word 2007/2010, the only file types that can contain VBA modules are .docm and .dotm (or files saved in 2003 format, at which point you can have .doc or .dot have code as well-- but in any event, you can't have code stored in .dotx or .docx format). You probably created the macros in your Normal.dotm.

    2) Quick Access Toolbar (QAT) customizations: these are typically stored on a per-user basis. However, you can, when adding items to the QAT, on the upper-right side of that dialog (under "Customize Quick Access Toolbar") choose to store the customization in the active document, rather than "For all documents (default)"

    3) Macro templates are "loaded" (i.e., made available for a user) in a couple of different ways. I am not sure from your description what the best solution is for you. Here are two ways to go about it

    A) Global Addin. Create a .dotm file, open it, add a module to that .dotm (*not* your Normal.dotm) and your two macros to that module. Then add the QAT buttons, but make sure you add the QAT buttons for that .dotm, not for all documents.

    Then, in order to "deploy" this global addin, you will need to have it automatically loaded by other user's Word. This requires having your global addin be loaded on startup. It will also require allowing security access -- not something you can program (since otherwise malicious programmers could also spread viruses in this manner). Depending on your set up, you may need to talk to your system administrators. But the area in Word to handle security is in Word Options > Trust Center > Trust Center Settings > Trusted Locations. And to know where the Startup path is (and thus, where your Global Addin would need to reside), would be under Word Options > Advanced > File Locations > Word Startup.

    B) Macro template. In this scenario, the macros would also be in a .dotm, but the macros would only be available when a document based on that .dotm is opened. So you would do much of what you do for #1, but that document would also contain the bookmarks and xrefs and vba code and QAT customizations. And then users would double-click on the .dotm to create a new document *based* on your template, and have access to the macros and QAT buttons. The same Trust Center settings would still apply, however.

    This is kind of a big area you're asking about, so some generalities are included in the answer. You want to know how to deploy your code to other people -- that means you need to more clearly define when and where the code should be available (in addition, how you want to update this code and/or template if/when there are bugs/refinements/enhancements/additions desired).
    Last edited by Frosty; 05-29-2012 at 10:58 AM.

  3. #3
    Frosty,

    Thank you for your input. I decided on your option B approach. I was able to create the module and add the macros as you suggested and then I was able to create the QAT buttons to run those macros. Works great.

    My question now is, how do I modify this .dotm file when I make future enhancements? When I double click the template from its file location it opens with the filename "Document1" and then when I attempt to save any changes I made it defaults to a .docx file type. Is there some special way of editing or making changes to .dotm templates?

    Thank you.

    Mark

  4. #4
    Sure thing. Either use file open from word to open the .dotm, or right-click on the file from windows explorer and choose open. You'll see that New is bolded on the right click menu... That is the default action for a .dotm or .dotx file-- to create a new document based on the template.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •