Using The Visual Basic Editor

When you change the name of a macro, Excel updates the Macro dialog box, but it does not update toolbar buttons and menu options that refer to the macro. Because you changed the name of the macro, you need to change the macro reference for each item to continue using the same button or menu option to reference the macro. To change the macro reference for these items, click ToolsOCustomize to display the Customize dialog, and then right-click the menu option or toolbar button to display a menu of options. Click the Assign Macro option to display the Assign Macro dialog box. On this dialog box, click to highlight the name of the macro that you want to use for the corresponding toolbar button or menu option, and then click OK.

Because the name of the macro changes, you may also want to change the name of the toolbar button or menu option to correspond to the macro name. To do this, you need to change the value of the Name field on the Customize menu. You can also change the hot key reference for the macro by typing & in front of the hot key for the macro.

RENAME A MACRO IN THE PERSONAL MACRO WORKBOOK

■ The Visual Basic Editor displays the source code for the current workbook.

-0 Click the PERSONAL.XLS project.

Q Close the Visual Basic Editor.

RENAME A MACRO IN THE PERSONAL MACRO WORKBOOK

^Q Click Tools O Macro O Visual Basic Editor.

■ The Visual Basic Editor displays the source code for the current workbook.

-0 Click the PERSONAL.XLS project.

Click the module containing the macro you want to modify.

_Q After the Sub keyword, type the new macro name.

Q Close the Visual Basic Editor.

■ The name of the macro changes in the Macro dialog box.

ATE A STARTUP MACRO

You can easily create macros that execute whenever you open a specific workbook in Excel. If you want the macro to execute every time you run Excel, you can place the macro in the Personal Macro Workbook. Of course, this type of macro executes only once, which makes it best suited for steps that you perform each time you run Excel.

A startup macro works great for setting the basic layout of your Excel window, such as the toolbars that you want to display and the desired locations. When it comes to the window layout, Excel always opens with the settings you used the last time you ran it. Therefore, any toolbars that you closed during the last session are closed when you open Excel again.

By creating a macro that sets the toolbars that you want to view, Excel opens these same toolbars and places them in the same location each time you run Excel.

The simplest method for creating this type of macro is to place the macro in the Personal Macro Workbook. Because Excel loads the Personal Macro Workbook each time you run it, the macro you create executes when Excel opens because that is when the workbook opens. You need to make the macro a part of the Personal Macro Workbook as well as a part of the ThisWorkbook object.

You must name the macro that you create Workbook_Open. If you have a macro with this name, Excel knows that whenever the object — the corresponding workbook — opens, the macro needs to run. See Chapter 4 for more information about Excel objects.

CREATE A STARTUP MACRO

CREATE A STARTUP MACRO

Record New Macro.

■ The Record Macro dialog box displays.

Record New Macro.

■ The Record Macro dialog box displays.

lh ype Workbook_Open in the Macro Name field.

El Click the H and then click This Workbook location in the Store macro in field.

-Q Click OK to record the

0 0

Post a comment