link a VBA macro to a button?

C

Chris Curvey

This has got to be a FAQ, but my search skills appear to be lacking
today.

I have written a little macro (is that the right term?) in VBA. When
I run it in debug mode, it brings up a form, you fill it out and press
the button, the VB code writes out a specially crafted file with the
info from the form, and all is well. Yay!

Now, how do I go about getting a button on one of the toolbars (or
ribbons, or whatever they are called now) to launch my form/macro/
whatever?

And on a related note, how do I distribute this new feature to
everyone else in my firm (I'm guessing I have to modify a default
template, but I could use some guidance)?
 
J

Jay Freedman

Since you didn't say, I'll have to guess that you have Word 2007 --
it's very different from previous versions in this respect.

I'll take the second question first, because it affects the answer to
the first question. To distribute the macro and its button, store both
of them in a template, and have each user save the template on their
PC in the folder %appdata%\Microsoft\Word\Startup.

Your macro is probably now in your Normal.dotm template, which does no
one but you any good. Create a new blank document and save it as a
macro-enabled template (something like mymacro.dotm). While it's still
open, go to the VBA editor, select that template's project in the left
pane, and click Insert > Module. Go to the Normal project, cut your
macro's code to the clipboard, and paste it into the module in the new
template.

The new template should still be open in Word. Right-click the Quick
Access Toolbar and choose Customize. In the left-hand dropdown in the
dialog, set the category to Macros. In the right-hand dropdown, choose
the new template as the place to store the new button you're about to
make. Click your macro's name in the list on the left to select it,
and click the Add button to send it over to the list on the right.

Optionally, select the macro's name in the list on the right and click
the Modify button. Edit the display name (this is what will be in the
tooltip when you mouse over the button) and choose a button icon --
and before you ask, no, you can't add more icons to that batch.

Save and close the new template, and close Word. Go to the folder
where you saved it, and move it to %appdata%\Microsoft\Word\Startup.
Then restart Word, and you should see your button on the Quick Access
Toolbar.

If I'm wrong about it being Word 2007, see
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm
for how to make a toolbar button in earlier versions. The part of the
task about saving the template in the Startup folder is the same as I
described above.

More about distributing macros in templates is at
http://www.word.mvps.org/FAQs/MacrosVBA/DistributeMacros.htm.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
 
C

Chris Curvey

Jay,

A very much delayed and heartfelt "thanks." That worked perfectly.
Excellent instructions.

-Chris
 
C

Chris Curvey

I'm not sure what I'm messing up, but for some reason I can't get this
installed on my end-user machines.

I have created the "macro-enabled template" saved it as "J:
\MyTemplate.docm".

Now I go visit Lester, one of my end users. I copy MyTemplate.docm
from J: to c:\documents and settings\lester\application data\microsoft
\word\startup.

And now I'm stuck. I go to the drop-down on the Quick Access Toolbar-
More Commands. Then I choose "Macros" from the drop-down list on the
upper left, but the list does not include my new macro.

I'm probably missing something stupid, can anyone help?
 
D

Doug Robbins - Word MVP

The template (MyTemplate.docm) must be open (Use File>Open and navigate to
the Startup Folder) for the macros in it to be available to be added to the
QAT. After adding the macro to the QAT, the template can be closed and the
macro can still be called.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

I'm not sure what I'm messing up, but for some reason I can't get this
installed on my end-user machines.

I have created the "macro-enabled template" saved it as "J:
\MyTemplate.docm".

Now I go visit Lester, one of my end users. I copy MyTemplate.docm
from J: to c:\documents and settings\lester\application data\microsoft
\word\startup.

And now I'm stuck. I go to the drop-down on the Quick Access Toolbar-
More Commands. Then I choose "Macros" from the drop-down list on the
upper left, but the list does not include my new macro.

I'm probably missing something stupid, can anyone help?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top