I recently added some macros into my PERSONAL.XLSB file for Excel and started getting these annoying pop-ups saying that it’s locked for editing. This file loads in the background every time you open a copy of Excel. The point of it is to give you a place to save commonly used macros that will then be loaded and available to you in whatever workbooks you open. The problem with this is that if you open multiple copies of Excel, they all ask the operating system for write permissions in case you want to update the file.
Tip: PERSONAL.XLSB loads as a “Hidden” file and you can see what other hidden files you may have by going to the View tab on the ribbon and pressing the Unhide button. If it is grayed out, you don’t have any hidden files.
Where Personal.xlsb is Located
Now, the Personal.xlsb file lives in your Application Data folder. Typically, you can find it here:
You may also find it somewhere like this:
C:\Users\Documents & Settings\Application Data\Microsoft\Excel\XLSTART\
If you can’t find it, try replacing “Roaming” with “Local” or do a search.
There are two main ways to fix this issue (that I’m aware of) and which to use depends on whether you actually use the file or not.
If you do not use the file:
Simply go into the folder above and delete the file. Once it’s gone, Excel will no longer try to load it and you won’t get this message.
If you do use the file:
If, like me, you find this file helpful, then you can make it read-only. This will force all Excel windows to open it only for reading, not writing. This resolves the conflict that having multiple windows open would normally cause.
To do this:
- Go into the folder above and find the file(s1).
- Highlight them and view their properties (Right-click -> Properties).
- Then, on the General tab under Attributes, check the “Read-only” box.
- Click “OK” and you’re done!
1 You can have more than one PERSONAL.XLSB file. For example, I had both PERSONAL.XLSB and PERSONAL-2.XLSB which would load at startup for me.
Please note that if you want to edit the file again, to add another macro for example, you’ll have to un-check the read-only box, update the file and then recheck it.
I think that for most users, this will be sufficient. If you’re editing your macro file every day, perhaps you need to consider making your macros more generic so they take parameters from, say, an InputBox. 🙂
Drop a comment if you have any questions!