Macro buttons suddenly stopped working

February 12, 2022
One Comment

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.