Code to Automatically Enable Macro

[Salza]

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?

Thanks.

Regards,
Salza

 

[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.

 

[snsyg]

salza,

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
automatically.

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
executed.

good luck,

kare

 

[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
itself.

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!