Setting up the Personal.xls in 2007

[matthewR]

What is the procedure for setting up the Personal.xls in 2007. Do you have to hide and unhide the personal.xls spreadsheet to add macros etc. Any information you can supply would be very helpful. We are supposedly getting 2007 on September 28.

[HansV]

The personal macro workbook in Excel 2007 is named Personal.xlsb. As in previous versions, it doesn't exist in a clean installation. You create it as follows.

First, you need to make sure that you can work with macros. You need to do this only once:
- Click the Office button (the equivalent of the File menu in Excel 2007).
- Click Excel Options.
- Click Popular in the navigation pane on the left hand side.
- Tick the check box "Show Developer tab in the Ribbon".
- Click OK.

x345.png

Now, we're going to create a macro.
- Activate the Developer tab of the ribbon.
- Click "Record Macro".
- Select "Personal Macro Workbook" in the "Store macro in" dropdown.
- Click OK.

x346.png

- The caption of the "Record Macro" button changes to "Stop Recording".
- Click this button.
- Click the "Visual Basic" button on the ribbon or press Alt+F11 to activate the Visual Basic Editor.
- You will see (among others) "Visual Basic Project (PERSONAL.XLSB)". This is the personal macro workbook.
- Click on this item.
- Click the Save button in the toolbar or press Ctrl+S. You have now saved your personal macro workbook.
- If you expand Personal.xlsb, then Modules, and double-click Module1, you'll see the empty macro that you have recorded.

x347.png

- You can delete this macro or edit it, whichever you prefer.
- Please note that it wasn't necessary to unhide the personal macro workbook to see the macro; you just need to activate the Visual Basic Editor.

[matthewR]

Thanks Hans.

[Sundog]

Hans, how does one edit the Personal.xlsb so a new Workbook retains formatting, header/footer, etc? Or does Personal.xlsb only exist to store macros available to new Workbooks?

[HansV]

The only purpose of Personal.xlsb is to store macros that are available in all workbooks (not just new ones), it is not the "default template" for new workbooks.

To create a default template:
- Set up a workbook with the number of sheets, the formatting, headers/footers etc. that you want.
- Save this workbook as a template named Book.xltx in your XLStart folder.
- If the workbook contains macros, save it as a macro-enabled template Book.xltm in the same folder.

The name Book is obligatory. This template will be used when you create a blank new workbook.

You can also create a template with a single worksheet as Sheet.xltx (or Sheet.xltm) in the same folder. It will be used when you insert a new worksheet into a workbook. Again, the name Sheet is obligatory.

Notes:

The XLStart folder is C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLSTART in Windows XP, and C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART in Windows Vista and Windows 7.

The names Book and Sheet are language-dependent. In localized versions of Excel, you must use localized names.

[Sundog]

Thanks for the explanation, Hans.

[Jan Karel Pieterse]

First, you need to make sure that you can work with macros.

Not really, there is a tiny button on the statusbar to start/stop recording:

[HansV]

Thanks, that's a useful tip!

But I'd still recommend to show the Developer tab on the Ribbon.