Code to Automatically Enable Macro



Hi all,

What is the 'macro code" to automatically enable a macro when opening an
Excel file that contains macros.?
I need it so that users who open my file (that contain macros) don't have to
set the security level to 'medium" everytime thay use on different PC.
Do I add in this code ? In Micrososoft Excel Objects - Workbook - Open
using VB editor?





Ken Wright

Previously posted by Tom Ogilvy:-

If you could disable the users ability to enable or disable macros, you
would defeat the purpose of having that capability at all.

The only way you can disable the prompt is to certify you project in xl2000
or later (not supported in earlier than xl2000) and have the user accept
your certificate as a trusted source, or to lower the security setting (have
the user do it).

Alternatives are to use Addins or workbooks placed in the xlstart folder. In
the latest versions of Excel, I believe the user even has to OK the loading
of Addins as a security setting.



if i am understanding your question correctly, i believe what you ar
wanting to to is automatically set the security level to medium with n
user interaction at all. they open the file and start working and th
security code is already at medium.

to do this, you need to write a macro called Sub Auto_Open()

whatever code you have in this macro will execute automatically whe
the file is opened.

if you don't have the code written yet for changing the security leve
to medium, you can select the "record macro" command, name i
Auto_Open, then do the steps in excel needed to change the securit
level, then select "stop recording". this will write the code for yo

also, if you want some thing special to happen when you close a file
you can use a macro named Sub Auto_Close()

i have a file that has a custom toolbar which is to be used only wit
that file. i don't want the toolbar to be on all the time. i want i
automatically displayed when i open the specific file, then t
automatically turn off when i close that file.

here's the code i used for that:

Sub Auto_Open()
Application.CommandBars("Bid Check").Visible = True
End Sub

Sub Auto_Close()
Application.CommandBars("Bid Check").Visible = False
End Sub

hope this helps.

by the way, you can keep adding whatever code you want to the aut
open/close, if you have multiple things you want automaticall

good luck,


Ken Wright

If the security settings are not set to low, then this macro will never get
initiated until the user has done what Salza is trying to avoid having to do, ie
enable macros, which would therefore defeat the purpose of the auto_open routine
you suggested in the first place.



Dave Peterson

Another alternative is to have the users open a dummy workbook that opens your
real workbook via code.

If the user has macros disabled, then the "dummy" macro won't run and the real
workbook won't be opened.

If the user enables macros, the dummy macro opens the real workbook and closes

In the dummy workbook, have a single worksheet that reads:
Close this workbook and reopen--but ENABLE MACROS!

The users might see a flash when things work the way you want.

The dummy macro could look something like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book1.xls"
ThisWorkbook.Close savechanges:=False
End Sub

When you're testing, save first. When it runs, it closes itself without saving!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads