Macros in 2011/6 xlsm disabled on another computer with 2007 despite trusted location & macros enabled

[davidruben]

I have a macro enabled xlsm spreadsheet created in Excel 2011, and following a software update to Office 2016 last opened & saved in that. Finding myself now working from home due to Covid, I emailed myself the file to continue working at home (where I have Excel 2007) to develope this further, but find this one file reports Excel macros are disabled.

a) Any other macro-containing file (either excel 2007 xls or excel 2011 embeded macro xlsm) that I have at home and saved in same folder run macros fine, just this one file wont auto run on opening as selected, nor from within Alt+F11 can I manualy run macros - a pop up states "Because of your security settings, macros have been disabled. To run macros, you need to reopen this workbook, and then choose to eenable macros. For more information about enabling macros, clinck help".
- there is no initial pop up asking whether to enable macros for this file
- the help button in the pop up now just links to a Microsoft page stating that Excel 2007 is now retired
- macros are enabled in Excel on this computer and run fine for all other file, so what "security settings" are blocking this one file ?

b) In Trust Centre
> Trusted Locations - I have add the folder location that the file is saved in
> Add-ins - "Require Application Add-ins to be signed by Trusted Pulishers" and "Disable all Application Add-ins" are both unticked
> ActiveX settings - I have ticked "Enable all controls without restrictions" - I normally have "Prompt me before enabling all controls", but changing this hasd not helped
> Macro settings - set to "Enable all macros"
> External content - changing to allow "Enable all data connections" did not hepp

c) I can record new macros to this same file, but can't then run them

d) in Alt+F11 I see that "Design mode" icon is set and if I try to click to exit that mode then a popup box announces "The macros in this project are disabled. Please refer to the online help or documentaion of the host application to determine how to enable macros". Opening Excel normally or with another file shows that as a default this is not set.

e) using remote access to work computer the original file opens and runs macros fine, so not a file corruption

f) Lastly having opened the file, Saved As an Excel 2007 xls file, that copy of the workbook has normal working macros, Alt+F11 does not show in design mode. However if I take that xls file and now Save As to a new embeded xlsm file that again reports macros are not enabled in Excel and design mode is again showing as set.

So what setting is blocking one specific xlsm file from having macros enabled despite Excel set to enable all macros and from the folder location, and seemingly in a fixed design mode ? Other than having to create the spreadsheet from fresh is there any "hidden" attritute being saved in the file irrespective of file format chosen ?
(I'd rather not recreate as it has 9 sheets of annual calanders for working & leave details for 30 staff)

 

[Jon Peltier]

Navigate to the file in Windows File Explorer. Right-click on the file, and choose Properties. At the bottom of the dialog is a notice that the file came from a different computer and has been blocked. Check the Unblock box and click OK. You should now be able to use the VBA in this file.

 

[davidruben]

Navigate to the file in Windows File Explorer. Right-click on the file, and choose Properties. At the bottom of the dialog is a notice that the file came from a different computer and has been blocked. Check the Unblock box and click OK. You should now be able to use the VBA in this file.

A good suggestion, but in this case properties do not show any notice that this came from another computer:

 

[Kerryx]

ok just wondering what the macro is doing is it connecting to external data source like a website to get information, if yes check your AV software and set filename up as an exception to allow it to bypass default AV settings.

 

[davidruben]

ok just wondering what the macro is doing is it connecting to external data source like a website to get information, if yes check your AV software and set filename up as an exception to allow it to bypass default AV settings.

I've rechecked and has no external calls websites nor other files, and does not make use of ActiveX controls nor Windows API.

 

[Jon Peltier]

Does it try to access objects in other programs, such as a word document? That was one of the first things I suspected.

 

[davidruben]

Does it try to access objects in other programs, such as a word document? That was one of the first things I suspected.

No references to external documents or programs.

 

[davidruben]

Just tried the same file on home laptop which also has Excel2007 and that allowing macros to be run - so not a problem with the file but with desktop excel settings... yet same trust centre settings allowing for location, enable all macros - I'm obviously missimng something...

 

[Jon Peltier]

So I guess it isn't anything to do with the file or its code.

Rename the file on your home computer, then copy the file with the new name to your other computer, and see what happens.