A question we receive quite often is: “Why have my macros stopped working?” This happens most often when a macro-enabled workbook is shared with another user.
There are several reasons why this can happen, and we’ll look at all of them in this article.
The workbook may have been re-saved as an xlsx file
Since Excel 2007, Excel files have been split into two types. Macro-enabled workbooks have the .xlsm file extension, while ordinary workbooks have the .xlsx extension. Only a macro-enabled workbook can contain macros.
If you have shared a macro-enabled workbook with another user and the macros subsequently stopped working, it’s possible that they accidentally re-saved it as an ordinary workbook that can’t contain macros.
You can see more about the different file types that Excel can save in our completely free Basic Skills E-book.
Macros may have been disabled in Excel
When you first open a macro-enabled workbook that you didn’t create, Excel usually disables macros and asks you to confirm whether they should be enabled.
You should never enable macros if a macro-enabled workbook comes from a source that you don’t trust. Macros have the potential to cause damage to your computer and can be the source of macro viruses.
This message doesn’t always appear. Particularly security-conscious users can completely disable macros using Excel’s options. To access these options, click:
File > Options > Trust Center > Trust Center Settings > Macro Settings
If this is set to Disable all macros without notification all macros will be disabled and the message offering to enable them won’t appear.
If another user can’t use your macros, this setting may be to blame!
Macro security is explained in great depth in our Expert Skills Books and E-books.
The macros may be in your Personal Macro Workbook
When you create a macro you have the option of saving it into the current workbook or into your Personal Macro Workbook. The Personal Macro Workbook is a repository of macros that is always available to you regardless of which workbook you have open, but it resides on your computer and won’t be available to anyone on a different computer.
If your macro-enabled workbook doesn’t work for another user it may be because the macros are in your Personal Macro Workbook and therefore inaccessible to other users. It’s important to make sure to save macros into the workbook itself if you need to share it with others.
Recording macros and using the Personal Macro Workbook is fully explained in our Expert Skills Books and E-books.
Workbook protection may be preventing macros from working
It’s possible to ‘protect’ your workbooks in many different ways, including allowing you to prevent certain cells from being selected or edited.
Macros respect protection, and won’t be able to affect cells if they have been protected. If you’ve protected the workbook before sharing it with another user this may be the reason that macros don’t work for them.
Protection and security is covered in depth in our Expert Skills Books and E-books.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.