Suppressing Enable Macros QUestion

[MWE]

I have an application, A.xls, that opens a series of workbooks
examines certain things, saves them if anything was changed and the
closes them.

Some of these workbooks have macros and, thus, the "Enable Macros
question appears when they are opened. Is there a way to suppress tha
question such that no manual interaction is required while A.xls runs?
None of the macros in the target workbooks will be run as part of wha
A.xls does.


Thank

 

[Robin Hammond]

1. change your security level settings. Probably not good.
2. create a digital certificate using selfcert.exe and attach it to each
project, then you should be able to elect to always trust content from the
certificate provider, and the warning should stop appearing for the books in
question.

Robin Hammond
www.enhanceddatasystems.com

 

[count]

Two solutions here:
1. Install Office option Digital Signature (if not already installed). It
provides program SelfCert.exe which you run once to issue yourself a
certificate for own use. Then in VBA Editor go Tools, Digital Signature -
you are done. This forum can also tell you how to transfer certificate
between PCs if necessary - enquire within
2. Lower macro security in Options, Macro Protection - NOT RECOMMENDED these
days.
hth

 

[Bob Phillips]

If you have XL2002 you could set the security setting to low (not
recommended), otherwise the only way is to get digital signing (probably not
feasible). It's life I am afraid.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[MWE]

*1. change your security level settings. Probably not good.
2. create a digital certificate using selfcert.exe and attach it t
each
project, then you should be able to elect to always trust conten
from the
certificate provider, and the warning should stop appearing for th
books in
question.

Robin Hammond
www.enhanceddatasystems.com

*****
original post clipped
*****
*

Thanks for the suggestions. I agree that lowering the security leve
is not wise. I tried the Digital Signature approach and it does no
work. It adds some overhead -- the base application now tries t
connect to the web at startup; probably trying to verify the digita
signature. The "enable macros" prompts for the target spreadsheet
still appear. These spreadsheets were not created by me. Also, th
macros are apparently Excel4 macros if that makes any difference.

I tried adding Application.DisplayAlerts = False. That strangel
disabled the macros in the target spreadsheets, but a different warnin
message appeared (which is just as annoying).

It would seem reasonable that there exists some way to programmaticall
disable any macro as the file is opened

 

[Robin Hammond]

First, I doubt that this will work with Excel 4 macros. The signature is
something that is added to the VBA project, and VBA did not exist way back
then.

Verifying the digital signature shouldn't result in a web connection,
however. Try running certmgr.msc from the start menu run prompt and look in
the personal, certificates folder to see if the signature you created is
there. If not, right click and try and add it from the file created by
selfcert (in your office folder), or just have another go with selfcert. I
just tried it and it added the new signature automatically.

Other than that, with Excel 4, I'm stumped, particularly since I am having
trouble getting the enable macros dialog to display when opening a file from
code on XP. I even went back and had a look at the excel4 macro reference to
see if there was an option to disable macros on opening.

No doubt there is a simple solution.

If the macros are not being run in the files in question, and they are that
old, is it feasible to remove them from the files, or to replace them with
VBA code which you can sign?

Robin Hammond
www.enhanceddatasystems.com

 

[Nick Chadwick]

In case you haven't come across the solution to this after 3 months, th
code you need is:

Application.AutomationSecurity = msoAutomationSecurityForceDisable

This will automatically and silently disable macros in any workbook
you open in your code