Macro Workbook

You can delete macros that you no longer use from the Personal Macro Workbook. The Personal Macro Workbook stores macros that you want to make available to all workbooks. Excel creates the Personal Macro Workbook when you store your first macro in it. After Excel creates the Personal Macro Workbook, the workbook opens as a hidden file whenever you run Excel. You can only tell that a hidden file exists by viewing the Macro dialog box, where Excel lists the macros used by the Personal Macro Workbook.

Excel stores the Personal Macro Workbook as a file named Personal.xls with a typical path of C:\documents and settings\ user_name\Application Data\Microsoft\Excel\XLSTART folder.

If you try to delete a macro out of the Personal Macro Workbook from the Macro dialog box, Excel displays a message box with the message, "Cannot edit a macro in a hidden workbook. Unhide the workbook using the Unhide command." By default, Excel does not allow you to delete macros out of hidden workbooks. Because Excel hides the Personal Macro Workbook, you cannot delete the macros in it without first unhiding the workbook using the Excel Unhide command.

After you delete the macro from the workbook, make sure that you hide the workbook again. If you do not hide the Personal Macro Workbook again, the workbook appears as an open workbook. Because you only use this workbook for storing globally used macros, you do not want to make other types of modifications to it. By hiding it, you keep it out of the way and eliminate the possibility of having unwanted changes made to it.

You can also use the Visual Basic Editor to remove macros from the Personal Macro Workbook. See Chapter 2 for more information about removing macros using the Visual Basic Editor.

DELETE FROM THE PERSONAL MACRO WORKBOOK

DELETE FROM THE PERSONAL MACRO WORKBOOK

January February March April

July

Au yust

Septembe

October

Novembei

Decembei

Ln Click Window O Unhide.

Ln Click Window O Unhide.

Insert Format i a a I a a ? a * n, u Is f, =SUM(B3:G3)

Unhide workbook:

January February March April

Insert Format i a a I a a ? a * n, u Is f, =SUM(B3:G3)

Unhide workbook:

July

Au yust

Septembe

October

Novembei

Decembei

- PERSONAL

■ The Unhide dialog box displays a list of open workbooks that are currently hidden.

hi jJJ"

fed ifa | Typically, you do not share the Personal Macro Workbook with other users.

Excel creates a different Personal Macro Workbook for each username on a machine. If you have multiple users on your computer with different usernames, Excel creates a different Personal Macro Workbook for each user. You can share a Personal Macro Workbook between different users, even on the same computer, by copying the workbook. You can use the Windows Explorer and copy the workbook from one user to another if you want to make the macros in that workbook available to other users. To do so, make sure you copy the PERSONAL.XLS file to the C:\documents and settings\user_name\Application Data\Microsoft\Excel\XLSTART folder from the user workbook you want to share to each user's corresponding folder. Keep in mind, you can only have one PERSONAL.XLS file for each user. If a user already has a Personal Macro Workbook, you can overwrite it with the new one. Of course, if you overwrite an existing Personal Macro Workbook, Excel no longer makes any macros you store in the workbook available. To eliminate potential problems you should rename the existing workbook so that a user can still access it if necessary.

How Arrange Position Excel Pdf

■ The Personal Macro Workbook is unhidden and displays in the Excel Window.

—0 After modifications are complete, click Window O Hide.

■ The workbook is hidden.

■ The Personal Macro Workbook is unhidden and displays in the Excel Window.

□ Click Delete to remove the macro from the workbook.

Note: For information about deleting a macro, see the section "Delete a Macro."

—0 After modifications are complete, click Window O Hide.

■ The workbook is hidden.

ASSIGN A MACRO TO A TOOLBAR BUTTON

You can assign any macro to an Excel toolbar. Excel uses toolbars to provide quick access to commonly used commands. You can make macros more accessible by creating a button on a toolbar to execute a macro. By doing this, you also do not have to remember the shortcut key that launches the macro. If you create a macro toolbar button, each time you want to run the macro, you simply Click the appropriate button.

When you add a button to a toolbar, it remains on that toolbar for all the workbooks you open in Excel. In other words, even if the active workbook does not have access to the macro because you closed the corresponding workbook, the Toolbar button still displays. For that reason, you should assign a macro that exists in your Personal Macro Workbook to a toolbar button to make the macro available from all workbooks. Remember that the Personal

Macro Workbook stores commonly used macros, and opens as a hidden file each time you run Excel. Excel always keeps the Personal Macro Workbook open and, therefore, makes any macros you have in the Workbook always available for use by other workbooks that you open. For more on macro storage, see the section "An Introduction to Macros."

You can add buttons to any of the existing Excel toolbars, or you can create new toolbars for your buttons. By creating a separate toolbar for your macros you keep your custom macros together in one location and you avoid ruining existing toolbars. You can select the toolbars to display in Excel on the Customize dialog box. Of course, you need to display a toolbar in Excel before you can add buttons to it. You also display the Customize dialog box to add buttons to toolbars.

ASSIGN A MACRO TO A TOOLBAR BUTTON

ASSIGN A MACRO TO A TOOLBAR BUTTON

Ln Click Tools O Customi

□ Click the Custom Button option.

Ln Click Tools O Customi

■ The Customize dialog box displays options for updating menus and toolbars.

-0 Click the Commands tab.

^0 Click Macros.

□ Click the Custom Button option.

—0 Drag the option to the desired location on the toolbar.

-■ As you drag the button across the toolbar, Excel inserts a line to indicate the location.

0 0

Responses

Post a comment