How to copy macros from one workbook to another
 |
|
|
|
| Start the Visual Basic Explorer |
|
(1) Open both the file that contains the macros to be copied and the one to copy to.
(2) Press Alt + F11 (hold down Alt and press F11) to start the Visual Basic Editor. |
|
| Display the Project Explorer |
|
(3) Press Ctrl + r to display the Project Explorer.
(4) In Project Explorer, find the module that contains the macro to copy.
If you don't see it, click the small + symbols below or aside the file name.
 |
|
| Drag to copy the macro module from one workbook to another |
|
(5) Drag the module to the workbook it is to be copied to and release.
In the example above, module Module1 is being dragged from IsColourFunction.xls to CallOuts.xls.
(6) Click on the name of the workbook copied into.
(7) Click the Save icon or press Ctrl + s to save the workbook.
(8) Close the Visual Basic Editor. |
|
Back to top
How to copy macros into the Personal Macro Workbook from another file
|
|
|
|
| Start the Visual Basic Explorer |
|
(1) Open both the file that contains the macros to be copied and the one to copy to.
(2) Press Alt + F11 (hold down Alt and press F11) to start the Visual Basic Editor. |
|
| If PERSONAL.XLS / PERSONAL.XLSB does not exist, create it |
|
(3) Press Ctrl + r to display the Project Explorer.
Do you see PERSONAL.XLS or PERSONAL.XLSB listed? If not, do the following:
(3a) Press Alt + F11 to return to Excel.
(3b) In Excel 2000 / 2002 (XP) / 2003 choose Tools | Macro | Record New Macro... In Excel 2007/2010 choose Developer | Code | Record Macro (Alt + LR). A dialog box appears.
(3c) Change Store macro in to Personal Macro Workbook.
(3d) Choose OK.
(3e) In Excel 2000 / 2002 (XP) / 2003 choose Tools | Macro | Stop Recording. In Excel 2007/2010 choose Developer | Code | Stop Recording (Alt + LR)
(3f) Press Alt+F11 to return to the Visual Basic Editor.
PERSONAL.XLS or PERSONAL.XLSB will now be there. |
|
| The Project Explorer |
|
(4) In Project Explorer, find the module that contains the macro to copy.
If you don't see it, click the small + symbols below or aside the file name. |
|
| Copy the macro into PERSONAL.XLS / PERSONAL.XLSB |
|
(5) Drag the module to PERSONAL.XLS / PERSONAL.XLSB and release.
(6) Click on PERSONAL.XLS / PERSONAL.XLSB to select it.
(7) Click the Save icon or press Ctrl+s to save it.
(8) Close the Visual Basic Editor. |
|
Back to top
How to type a macro into a workbook
A macro must be entered on a Module sheet. You can type it into an existing
module sheet if one exists or you can insert a new module sheet.
|
|
|
|
| Start the Visual Basic Explorer |
|
(1) Open both the file that contains the macros to be copied and the one to copy to.
(2) Press Alt + F11 (hold down Alt and press F11) to start the Visual Basic Editor. |
|
| Display the Project Explorer |
|
(3) Press Ctrl + r to display the Project Explorer.
(4) In Project Explorer, select the workbook that is to contain the macro.
(5) If you are going to enter the function into an existing module sheet,
find it in Project Explorer and double-click it. The module sheet appears in the
window to the right. Note that in order so see any module sheets you might
need to click the small + symbol buttons. You can type the macro code at
the bottom of the module.
(6) If you want to create a new module sheet, choose Insert | Module.
A blank module sheet appears in the window to the right. |
|
 |
|
| Type the macro |
|
(7) Simply type the macro code instructions. To start a new line, press Enter.
To indent press the Tab key.
(8) Save the workbook and close the Visual Basic Editor. |
|
Back to top
Assign or change a macro shortcut key
|
|
|
|
| Change the shortcut key |
|
To enter or change the shortcut key:
(1) In Excel 2000 / 2002 (XP) / 2003 choose Tools | Macro. In Excel 2007/2010 choose Developer | Macros (Alt + LPM)
(2) Select the macro.
(3) Choose Options.
(4) Enter a letter for the Shortcut key. You can only use letters A-Z.
Note that there is a difference between lower and upper case letters. Note also
that if you choose a letter that Excel has for a shortcut, the macro takes
precedence. Excel uses most of the lower case letters but few of the upper
case ones. |
|
Back to top
Create a toolbar icon to run a macro
In Excel 2000 / 2002 (XP) / 2003
|
|
|
|
| Display Customize dialog box |
|
(1) Choose View | Toolbars | Customize. A dialog box appears as below.
(2) Choose the Commands tab.
(3) Select the Macros category.
(4) Drag the Custom Button icon, which is a yellow smiley face,
onto the toolbar at a position you want it.
 |
|
| Assign macro to button |
|
(5) Right-click the icon on the toolbar. A menu appears.
(6) Choose Assign Macro. A dialog box appears. Select
the macro to assign from the list that appears. You may first need to
change the Macros in box to All Open Workbooks. Choose OK to
close the dialog box. |
|
| Edit the button image |
|
(7) Right-click the icon again. The same menu appears. Choose Edit Button Image. A dialog
box appears that allows you to edit the image. Use your undoubted
graphical skills to draw a suitable icon. Note that the Clear button
clears the existing image. Choose OK to close the dialog box. |
|
| Enter tooltip text |
|
(8) To change the tooltip text (that which appears when the
mousepointer is over the button), right-click to display the menu again
and in the Name box within the menu enter the text to appear. Press Enter. |
|
|
In Excel 2007/2010
|
|
|
|
| Create a button on the Quick Access Toolbar |
|
Excel 2007/2010 no longer support toolbars, however we can add a button to run a macro to the Quick Access Toolbar (QAT)
(1) Right-click the QAT and select Customize the Quick Access Toolbar...
(2) The Excel Options dialog box appears. In the Choose commands from drop-down box choose Macros.
The list on the left will now display all the macros from currently-open workbooks.
(3) Select the macro you wish to add from the list on the left and choose Add>> to include it in the Customize Quick Access Toolbar list to the right.
(4) While the macro is still selected, choose Modify...
A selection of icons appears. Unlike in previous versions of Excel, we cannot create our own buttons but have to choose one from the selection. |
|
 |
|
| |
(5) Select a suitable button for the macro.
(6) Enter an appropriate tooltip into the Display name box.
(7) Choose OK to close the Modify Button dialog box, choose OK again to close the Excel Options dialog box.
|
|
Back to top
Attach a custom toolbar to a workbook
Excel stores an attached custom toolbar in the workbook. Therefore, the toolbar
can be recovered if need be. The toolbar can also be transferred to
another PC.
In Excel 2000 / 2002 (XP) / 2003
|
|
|
|
| Select the workbook, if it is hidden, unhide it and, if necessary, reassign macros |
|
(1) Select the workbook to attach the toolbar to. If it is a hidden workbook (such as
Personal.xls), unhide it. To do that, choose Window | Unhide.
(2) If you have copied macros to the workbook and any of the toolbar icons are to
run those macros, make sure the icons are assigned to the macros of that
workbook. To check and or change that, display the Customize dialog box
such as you see below, right click each icon and choose Assign Macro. |
|
| Display Customize box |
|
(3) Choose View | Toolbars | Customize. A Customize dialog box appears.
 |
|
| Attach toolbar |
|
(4) Select the Toolbars page.
(5) Choose Attach. The Attach Toolbars dialog box appears.
(6) Select the toolbar to attach.
(7) Choose Copy >>.
Note that if the toolbar name already appears in the Toolbars in workbook
box, it is already attached. To reattach, first delete the entry in the Toolbars
in workbook box by selecting the entry and choosing the Delete
button. The Copy >> button above changes to Delete
when you select a toolbar in the Toolbars in workbook box. |
|
| Save the workbook |
|
(8) Choose OK to close the Attach Toolbars dialog box.
(9) Choose Close to close the Customize dialog box.
(10) Save the workbook. |
|
| For PERSONAL.XLS, hide and save from Visual Basic Editor |
|
(11) If the workbook is Personal.xls, hide it by choosing Window | Hide.
Then, press Alt + F11 to start the Visual Basic Editor, choose View | Project
Explorer, select PERSONAL.XLS and click the Save icon to save that file.
Close the Visual Basic Editor. |
|
Back to top
|