Enable / Disable Macros behaving differently in Excel 2003 from Windows XP to Windows 7

Hi,

Bit of a strange one this that I can't find an answer to and wondering if anyone here has come across it before.

Until recently I was using Windows XP with Excel 2003.
I have several Workbooks with Macro's that open other workbooks to retrieve data. One of these actually calls the macro's within the other workbook (to update figures) before retrieving data.

I understand the basic principle of security settings (set to medium) that you cannot bypass the "Enable/Disable Macro" option for obvious reasons. HOWEVER, I never questioned it at the time, but if the main workbook is opened with Macro's enabled, when it opens the source workbooks, the macros are automatically enabled and no warning message shows. Therefore I've happily coded away and thought nothing of it.

Last week I was migrated (at work) to Windows 7, but have remained using Excel 2003.
Now, when I open the same workbook and run the macro - the "Enable/Disable Macro" option is displayed each time an underlying source workbook is opened. This obviouly halts the code and throws out errors if the user clicks on disable - so I need to find a workaround.

I checked macro security settings in Excel - both at Medium.
Investigated using Digital Certificates - only works for one user so cannot be rolled out to all users and the trusted users list is locked down.
I even tried just to surpress the message and leave macros disabled in the source workbooks but this doesn't work either. Code used:

Application.DisplayAlerts = False
Application.EnableEvents = False

...then set to true after opening. Nope, the "Enable/Disable Macro" option still pops up.

Does anyone have any ideas why this is happening - what to check - or how to workaround (e.g error handling if not enabled).

Argh! Driving me crazy!

Hi JosephP,

Thanks for coming back on this. Unfortunately Low Secutiry is not an option in this case.Too risky to be justified and it's locked to Medium as the lowest setting anyhow.

What's strange is Medium in XP didn't trigger the message but it does in Windows 7 - also that I cannot surpress the message and automatically disable macros... If I could do that at least I could keep the code running and work around it...

Appologies I made an assumption there! Won't do that again...

It does indeed work - you are a wonderful wonderful person and posibly the saviour of my sanity!!! Thank you kindly.

Just wondering - do I need to reset back to Medium / default settings before exiting the code?

Guess it does this automatically but is it best in terms of performance / etiquette?

Okay will do. Thanks again.