Checking for security level to allow macros to run

[Guest]

Is there any way to check programmatically, if the security level is set to
allow macros to run, and then 1) display a message indicating that the
security level must be set to medium and/or the macros must be enabled; and
2) stop any activity on the workbook and force the user to restart excel with
macros enabled?

 

[Ron de Bruin]

Hi Klaus

You can hide all sheets exept one
On that sheet you put a messege like "you must eneble macro's to work with this workbook"

Then in the workbook open event unhide the sheets and in the before save event hide them

 

[Don Wiss]

Is there any way to check programmatically, if the security level is set to
allow macros to run, and then 1) display a message indicating that the
security level must be set to medium and/or the macros must be enabled; and
2) stop any activity on the workbook and force the user to restart excel with
macros enabled?

This is what I use (unwrap wrapping in MsgBox):

Function DoesProjectExist(AddInName As String) As Boolean
' addin name is case sensitive. is project name, not file name
Dim W As Object
DoesProjectExist = False
On Error GoTo ErrSection
For Each W In Application.VBE.VBProjects
If W.Name = AddInName Then DoesProjectExist = True
Next
Exit Function

ErrSection:
MsgBox "In order for this to work properly you need to change a
setting. Go to Tools -> Macros -> Security... -> Trusted Sources tab. Then
both boxes at the bottom have to be checked.", vbCritical, "Macros Are Not
Trusted Source"

End Function

 

[Guest]

I'm not sure how I can use this technique to check for the security level.
Klaus

 

[Guest]

I can see this working for "Trusted Sources", but that is not my case. All I
can do is try to check for the Security Level. Any ideas?
Klaus

 

[Ron de Bruin]

Hi Klaus

A workeround is to hide all sheets exept one with a message
and unhide them in the workbook open event.
And hide them in the beforeclose event when you close the 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

Some example code to do this
Sheet 1 stay always visible (message sheet)

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

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


See this page for information about events
http://www.cpearson.com/excel/events.htm

 

[Guest]

Thanks I'll give that a try and see if I can get it to work.
Thanks for your patience. I'm slow but my work is poor.
Klaus

 

[Jake Marx]

Hi Klaus,

The only way to know if macros are enabled or not is to try it. If your
code is running, there is no point in checking the security level, as you
know macros are enabled. If macros are disabled, then there is no code in
the world that will work, as it will never run.

Ron's advice is sound, and is often used to "lock down" a workbook.
Basically, you lock down your workbook with protection, hiding the relevant
worksheets/etc. In the Workbook_Open event, you show all the sheets and run
whatever code you need to run in order to allow for user interaction. If
macros are disabled, your code will not be triggered, and all the user will
be left with is a message on the worksheet that he/she must enable macros in
order to use the workbook.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]