Excel Macro-Enabled workbook (.xlsm) macros disabled and no obvious way to re-enable

Question

I'm having trouble with Excel macros being disabled in a macro-enabled workbook. I have an Excel 2003 template file (.xlt) that includes macros. I then do the following:

1. Open the Excel 2003 macro enabled template workbook (.xlt) in Excel 2007/2010
2. Save the workbook as an Excel 2007 macro-enabled workbook (.xlsm)
3. Close and open the workbook in Excel 2007/2010

Then the macros are disabled and there is no obvious way to enable them.

The same problem seems to be described here: http://www.sqldrill.com/excel/miscellaneous-excel-subjects/872432-excel-2007-macros.html

I’ve tried saving to a ‘trusted location’ and the other suggestions in that thread but the same problem occurs.

Has anyone else come across this problem?

Thanks, Tom

Answer 1

from developer tab

1. Macro Security a. macro setting : Select disable all macros with notification b. Developer Macro Setting: Select "Trust access to VBA Project Object model"
2. Disable Design mode
3. close and reopen the xlsm
4. it will prompt to enable macro content, click yes
5. Thats all, your macro is runnning now.

Answer 2

I had a similar problem - macros were not working, none of my events fired, etc. Closing/reopening had no effect.

By opening another workbook with macros enabled and running some of them, my problem workbook mysteriously began working again.

Answer 3

The likely cause is in Trust Center -> Macro Settings, you have one of the "Disable..." radio buttons selected (which is the default setting). Try clicking the "Disable all macros with notification" so you can choose which .xlsm files you want to enable (or, if you are on a test machine and want to enable all macros, click "Enable all macros").

Answer 4

There is a simple but strange solution to this issue. Go to system tools-> disck cleanup and then clean temporary files and that will fix it

Answer 5

In addition to the Macro Security issue mentioned in @IndrajeetK's answer, another possible cause is that you don't have the VBA component installed.

• Navigate: Windows > Start > Control Panel > Programs and Features
• Select Microsoft Office (edition\version)
• Click Change
• Select Add or Remove Features
• Click Continue
• Navigate: Microsoft Office > Office Shared Features > Visual Basic for Applications
• Click the dropdown and select Run from My Computer
• Click Continue

If you still have issues, repeat the above only select Repair instead of Add or Remove Features.

If the above gives you a message configuration did not complete successfully, see this post on investigating the log.