Challenge Project 8-10

In this project, you explore how to use the PERSONAL.XLSB workbook, an option in the Store Macro In list in the Record Macro dialog box. PERSONAL.XLSB is a hidden workbook that is created and named automatically when you choose it as the location for a recorded macro. The workbook opens each time you start Excel, and its macros are available in any open workbook. (Note: If you are unable to access the PERSONAL workbook, verify that it is enabled. Click File, Options, and Add-ins. Then choose Disabled Items from the Manage drop-down list and enable the workbook if its name displays in the list.)

[Student Learning Outcomes 8.1, 8.2, 8.3, 8.4, 8.5, 8.8]

File needed: None

Completed Project File Names: [your initials] Excel 8-10.xlsx and PERSONAL.XLSB

Open a new Excel workbook and modify your workbook according to the following guidelines:

• Select cell A1 and record a macro with relative references named MyName. Use a shortcut of Ctrl+Shift+N. In the Record Macro dialog box, select Personal Macro Workbook to store the macro.
• Type your name, your street address, your city, state, and ZIP code in three rows, typical address style, and stop recording.
• Verify that a printer is available and ready.
• Write another macro named PrintIt. Use a shortcut of Ctrl+Shift+P and store the macro in the Personal Macro Workbook. Record your actions to print the active sheet; the sheet will print as you record the macro.
• Close the open workbook used for recording your macros without saving it. The PERSONAL macro workbook includes your macros.
• Open another new workbook and confirm that your printer is ready.
• Press Ctrl+Shift+N and then Ctrl+Shift+P. The PERSONAL.XLSB workbook is hidden, but its macros are available.
• Click the View tab and note that the Unhide button [Window group] is available.
• Click the arrow on the Macros button [View tab] and choose View Macros. The available macros list identifies the PERSONAL.XLSB! workbook. Select the MyName macro and choose Edit. You cannot edit a macro in a hidden workbook. Click OK in the message box and close the Macro dialog box.
• Click the Unhide button [View tab, Window group] to show the PERSONAL workbook. It has no visible data, only your macros. (If you are using a shared computer, the PERSONAL workbook may also include macros and documentation from another user.)
• Click the Macros button [View tab] and choose View Macros. Select the MyName macro and choose Edit. The Visual Basic Editor opens.
• In the VBE, edit your address. Add a personal title or your middle initial, a street direction, or spell out the state name. Be careful when editing code to not alter punctuation or commands unless you are familiar with Visual Basic for Applications programming. Close the VBE and return to the Excel window.
• Verify that you are in the PERSONAL workbook. From the View tab, choose Hide to hide the PERSONAL workbook. It is important that this workbook be hidden so that you do not accidentally enter data or unwanted commands.
• Delete data in the open workbook (or open a new workbook) and press Ctrl+Shift+N to run the edited macro. Save the Excel workbook as [your initials] Excel 8-10. Close the workbook.
• The Personal Macro Workbook remains available until you delete it. It is usually stored in Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART, which may be a hidden folder. To find and delete the Personal Macro Workbook, you must exit Excel. The file name does not appear in any Explorer window until you do that.
• Exit Excel and select Save in the message box to save changes to the Personal Macro Workbook. Do not restart Excel yet.
• Use a File Explorer window so that you can unhide the XLSTART folder if it is hidden. Click the File Explorer button on the Windows taskbar or choose File Explorer from the Start menu.
• Navigate to C:\ Users\UserName. UserName is the name for the computer at which you are currently working.
• If you do not see the AppData folder, click the View tab and select the Hidden items box. This command displays folders that are hidden.
• Continue navigating to find C:\ Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART. You should see PERSONAL.XLSB in this folder; file name extensions may be hidden on your computer. (Do not delete PERSONAL workbook if you are using a shared computer on which other users store macros.)
• Select and delete PERSONAL.XLSB. You cannot delete it if Excel is running because the file is open whenever Excel is running.
• Navigate once more to C:\ Users\UserName.
• Click the View tab, deselect the Hidden items box, and close the File Explorer window.