Using VBA to change macro security level

  • Thread starter Thread starter Tracey
  • Start date Start date
T

Tracey

I want to put in Auto_Open a procedure that will change the macro
security level. The problem is people will open a template and if they
have their security level set to high, they get a window that tells
them they can't run the macros. Many users don't know enough to simply
go change the security level, so I want it to automatically adjust it
for this workbook. The problem is I tried to record these steps to see
the code and it's one of those things the recorder doesn't pick up
(Toos/Macro/Security will not show up in recorded steps). The 2nd part
of the problem is if the high security level stops macros from running,
it seems to also stop built in procedures from running...
Any thoughts?
Thanks
tracey
 
Tracey said:
*I want to put in Auto_Open a procedure that will change the macro
security level. The problem is people will open a template and if
they have their security level set to high, they get a window that
tells them they can't run the macros. Many users don't know enough
to simply go change the security level, so I want it to automatically
adjust it for this workbook. The problem is I tried to record these
steps to see the code and it's one of those things the recorder
doesn't pick up (Toos/Macro/Security will not show up in recorded
steps). The 2nd part of the problem is if the high security level
stops macros from running, it seems to also stop built in procedures
from running...
Any thoughts?
Thanks
tracey *
Click to expand...

Hi tracey,

Unfortunately you cannot change the macro security settings by vba
code. If you could there wouldn't be much point in having the settings
in the first place.

What you could do is add another sheet with some text explaining that
you need to have macros enabled to run the workbook.in the visual basic
editor set the Minimize property for all of the other sheets to
hidden.

In your vba code you could set the visible status to true and hide the
sheet with the message warning that macros are needed, so that it
would only show the other sheets when the macros enabled.

HTH
 
Hi Tracey

It is possible when you open a real template file but
Don't do this.
Never try to change things like this on a users machine


A good way is to hide all sheets except one and run the macro ShowAll in
the workbook open event.
Place a message on that sheet(first one) with "you must enabled macro's to work with this file"
So the user can't use the workbook if he disable macro's.
If he do the workbook open event don't run so there are no sheets
to work with
run the sub HidealmostAll to hide the sheets in the beforeclose event.

You must protect your project also in the VBA editor because a user can't unhide
the sheets there also if you don't do that


Sub HidealmostAll()
Dim a As Integer
For a = 2 To Sheets.Count
Sheets(a).Visible = xlVeryHidden
Next a
End Sub

Sub ShowAll()
Dim a As Integer
For a = 2 To Sheets.Count
Sheets(a).Visible = True
Next a
End Sub
 
Tracey,

You can't change the security level via code.
If you think about it, if there was a way to open a workbook and have
that workbook change the security level, what good would it do to
have the security levels in the first place?

John
 
thanks everyone for your input. so how about this...how can I chang
the text in the automatic window that pops up telling them about th
macro security, I would like to add some bullet points for doing it.
thank you again
 
Back
Top