Writing Macros, Assigning Toolbar Shortcuts to Macros, 

and Assigning Floating Buttons to Macros

 

The most common Toolbar customization is the insertion of buttons that are shortcuts to user-recorded macros.  For example, if you always use 18-point Times New Roman for your Headers, instead of continually manually changing the font and point-size, you can record a macro, and then place a button on the toolbar to run the macro.

 

NOTE:  you can also place a floating button anywhere by using the Visual Basic toolbar (or the Forms toolbar which I never had much luck using), but this is used primarily for custom Excel macros, which are added only to that one document – NOT to all Excel docs ).

 

IMPORTANT - Protecting all your Hard  Macro and Customizing Toolbar Work - save your favorite macros in the "Normal.dot" template.  This will make sure that all macros are available to all documents.  Then save a copy of normal.dot somewhere in case it becomes corrupted, or you need to reinstall MS Office.  You will want to do a "Find" on your hard drive to find the file normal.dot since it is found in different areas depending on what version of Windows you are running and what version of Office you have.

 

Writing Macros

 

Don't write them - it's too tricky !!  Instead, record them as follows.  Some of the most useful macros, are ones that do things like placing a box around select text, inserting a line separator, changing the font to your favorite, etc:

 

1)  Click Tools/Macros . . . Record Macro - the following box will open:

 

 

2)  select where you want the macro stored:

        all documents  OR  your document (for special macros pertaining only to that document)

3)  decide whether or not to select a shortcut button or keyboard shortcut to the Macro, or to have no shortcut at all:

4)  click OK - a small toolbar with a pause and stop button will appear, as follows:

 

5)  perform whatever steps it is your are trying to make the macro perform

6)  click the STOP button to stop the recording

7)  DONE !!!

 

 

NOTE:  we do not go into advanced Visual Basic code here, which is the code used to write macors fopr Word and Excel.  To write more complex macros, you will need to edit the Visual Basic code, and this can be done by going into the Visual Basic Editor (Tools/Macros . . . select the macro . . . then click EDIT to open the VB Editor).  You should get a book to do this, as it is rather complex.  However, typical code such as "IF" statements, DO loops, etc - are fairly easy, and it can be a huge time saver on the job in many cases !!!

 

Editing the Toolbar Button (the shortcut that runs your new Macro)

 

Also see "How to assign a Word command or macro to a toolbar or menu" at:

 

As we stated before, when you drag the macro up to the toolbar, the initial shortcut button will be a text button and will be very long and not very descriptive.  In normal mode, you cannot edit the buttons on your toolbars - but you can once you have the "Customize" Toolbars dialog box open, you can:

 

1)  click View/Toolbars/Customize . . .

2)  right-click on the toolbar button you wish to edit

3)  you will now see a popup menu, and you click on any of the following options  (see the screenshot also):

 

 

 

 

 

Inserting Separators in Between Buttons on your Toolbar

 

A separator is just a grey line that denotes the border between two adjacent buttons.  It just makes your Toolbar look nicer, since you can see the buttons and should have no problem clicking on them, even without separators.


Method 1

View/Toolbars/Customize . . . or right-click on open space of Toolbar and select "Customize"
click and hold the mouse button down on a button that has no separator on the left
drag the button slightly to the right and release
Viola - separator appears to the left of the button !!

NOTE: in some applications, you may need to hold down the Alt key before dragging


Method 2

The Toolbars are sectioned up in "Groups", and each group has separator lines between them

- View/Toolbars/Customize . . . - or right-click on open space of Toolbar and select "Customize"
- right-click on the button that you want to add a separator to the "left" of
- select "Begin a Group" to check it
DONE !!!

NOTE: if you want separators on both sides of a button, add one to the left of the button as described, and add one to the adjacent button (the one just to the right of that button)

Removing a Separator
- View/Toolbars/Customize . . . - or right-click on open space of Toolbar and select "Customize"
- right-click on the button that you want to remove the separator to the "left" of
- select "Begin a Group" to uncheck it

 

Creating a Floating Button to run a Macro

 

There are two ways to do this. – using the Forms toolbar (Excel), or using the Visual Basic toolbar’s Control Toolbox. (Word)
 
In both Word and Excel,  the "Assign Macro" option isn't available for controls created from the "Control Toolbox".  That option is available only for controls created from the
"Forms" toolbar and only with the Excel Forms Toolbar !!  Although the controls look and function in very similar ways, they are, under the hood, very different things.
 
If you create your controls using the "Controls" toolbar, you have to use "View
Code" to get to the code, and execute your specific code there.  If you create
your controls from the "Forms" toolbar, you have the "Assign Macro" option
available, but fewer options in general.
 
When you righjt-click a button, only one created from the Forms Toolbar will have the "Assign macro" option listed.  
 
But you can "assign" a macro to a command button (which comes from the VB Toolbar “Control Toolbox”), indirectly, by naming the button the same name as the macro.  :



·        Naming the macro - double-clicking to bring up the VB Editor or right-clicking the command button in design mode, and selecting view code.
·        Naming the Button – right-click and select Properties – then rename it
But the easiest way is to simply make a call to the existing Macro. 
 
 

 

Method 1 - Excel) Using the Forms Toolbar

 

2)      Record or write a macro

3)      Click View/Toolbars/Forms

4)      Click the “Button” button in the toolbar

5)      Drag the mouse to create a new button

6)      Right-click the button and select “Assign Macro”

7)      Select the Macro

8)      Done

 

Method 2 - Word)  Using the Visual Basic Toolbar “Command Button”

 

Unfortunately, the Forms toolbar with Word does not allow you to assign a macro.  The only way to to use the VB Toolbar’s “Control Toolbax’s “Command Button”.

 

Goto View/Toolbars/Visual Basic . . .   to bring up the following toolbar:

 

 

 

Click the Control Toolbox button (see arrow above) to open that toolbar. 

 

NOTE:  by default you will be in “Design Mode” (which can be toggled On or Off by the upper left button on the Control Toolbox).  As you worlk on your new button, you can test it by clicking on the Design Mode button to go out of design mode, test the button/macro, then click the design mode button again to go back into design mode, make changes, etc.  Then finally close the toolboxes when done.

 

 

 

 

 

Click the “Command Button” (see arrow) to create a new floating button:

 

 

 

Editing the properties of the Button

 

 

 

 

 

Assigning Visual Basic Code to the Button

 

Whenever you create a new VB Command Button it will create a default empty macro, as follows:

 

Private Sub CommandButton1_Click()

 

End Sub

 

To make the macro do something you need to insert code.  Right-click the button and select “View Code” to bring up the Visual Basic Editor

 

 

 

Method 1 – make a call to the macro – simply make a call to an existing macro.  Unlike most other programming languages, you do  not use the “Call” command – it is understood, so you just enter the name of the macro, which will automatically call the macro.  For example, suppose you already created a macro to find an acronym from a large list:

 

Private Sub CommandButton8_Click()

'

' Find_Acronym Macro

FindAcronym             

End Sub

 

Note – do not use the parenthesis afterwards unless you need to pass arguments

 

IMPORTANT – Where the VB Code Goes

 

The button is an “object” and it will reference a private sub that must go into “Microsoft Word Objects/This Document” area.  The called macro will go in the Modules/New Macros area.  Do NOT move these macros to a different area or try to combine them into one area !!!   Here is a screenshot :

 

 

Method 2 – paste and rename (not recommended) - Copy and Paste the text from the Macro you already created into the new macro associated with your floating button.

 

Remove your old Macro – delete the lines of text for it, leaving only the macro associated with your floating button (i.e. the Visual Basic Control Box)

 

Rename the macro

 

–  to do this you must rename both the button and the VB macro.  But there really is no need to do this, and it is buggy !!  But here’s how – for example:

 

1)  goto the VB Editor, and change the line:

 

“Private Sub CommandButton1_Click()”  to  “Sub MyMacro()”

 

2)      go back to the Word file, making sure you are still in VN “Design Mode”, right-click on the button and select Properties.  Then goto the Name field and rename the button to the same name that you typed into the VB Editor for the Macro.

 

To Edit the Button Later

 

Bring up the two VB toolboxes, and click on the upper left button on the Control Toolbox to go into Design Mode.  Then you can make changes, aned click the button again to go out of design mode to test – then finally close the toolboxes when done.