LyteByte

If you are working with macros in Excel 2007, you must be aware of the personal.xlsb file. When you create a VB Macro in Excel, you will be given the option where you want to store. You can either store it in Personal Workbook (Personal.xlsb), New Workbook or This Workbook. If you want the macros to be available in all the Excel Workbooks, you must store the macro in Personal Macro Workbook.

You may face some issues like ‘cannot edit macro on hidden workbook’ and unhide personal.xlsb file.

First lets review some of the frequently asked questions and issues with Personal.xlsb from Excel 2007

Where is Personal.xlsb stored in Windows Vista and XP?

In Windows Vista, the Personal.xlsb is stored at C:\Users\user name\AppData\Local\Microsoft\Excel\XLSTART

In Windows XP its at C:\Documents and Settings\user name\Application Data\Microsoft\Excel\XLStart folder.

If the personal.xlsb file is located at this location then it would be automatically started every time an Excel file is loaded.

Creating Personal.xlsb for the first time.

If you are creating your first macro or if you are creating your first macro that is stored in Personal.xlsb the you would get the following question and just click Yes. (Note: you will be asked for this question after you have closed all other workbooks and when you try to close the final blank Excel window)

“Do you want to save the changes made to Personal Macro Workbook? If you click Yes, It will be available next time you start Microsoft Office Excel.”

Editing macros stored in personal.xlsb file

When you try to edit the macros stored in personal.xlsb, you may get a message saying ‘cannot edit a macro on a hidden workbook and unhide the workbook using the Unhide command’.

Unhide Personal.xlsb, Personal Macro Workbook

To unhide personal.xlsb, go to View tab and click Unhide under Windows Section.

Select Personal.xlsb and click Ok to unhide the personal macro workbook.

Important Note:

Unhide the personal.xlsb only when you want to edit the macros stored under that. If you want to record another macro that you want to store in personal macro excel file then you must use the same technique and hide it.

Other method to edit macros in personal.xlsb without unhiding it

Use the keyboard shortcut Alt + F11 to open the VBA editor and go to the Modules under VBAProject (PERSONAL.XLSB) and double click on the module to edit them.