Macro/Trust Center Settings

[Martin]

I have a spreadsheet created in Excel 2003 in which I display a message to
users indicating that the macro security level was not set to either Medium
or Low. Basically, it functions because the WorkbookOpen event does not fire
if the setting is on High. Now that I have moved "up" to Excel 2007, the
process does not work to display the message.

Is there a way to simply read the setting in the Trust Center/Macro
Security/Macro Settings? I am not looking to change the setting, just be
able to see what it is so that I can display an appropriate message.

Thanks.

 

[Gord Dibben]

In 2003 how did you display your message to users?

If macro security was set at High, no code would run.

I would think you could do the same in 2007


Gord Dibben MS Excel MVP

 

[Martin]

The file was set up in Excel 2003 to have all sheets hidden except for a
message. If security was set to High, the WorkbookOpen event would not fire,
displaying the message. If security was set to Medium or Low, the event
would hide the message sheet and unhide the other sheet(s). All this was
based on an idea I got from this forum.

I have tried the file with Excel 2007, but it does not work the same way.
It appears that the WorkbookOpen event fires regardless of how the macro
security is set in the Trust Center. This hides the message and makes it
appear that the file is ready for use, when it really is not.

Based on my testing, it looks like Excel 2007 will display different
messages/windows depending on which of the security settings is selected. I
was hoping to read those settings (not to change the settings) to display a
message to tell the user how to make the necessary changes to allow the file
to function for their workstation.

Thanks.

 

[Gord Dibben]

Martin

I cannot replicate the problem.

I built a workbook in 2007 and added this code to Thisworkbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = True
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub

I then set security to "Disable all macros without notification"

No code runs when I open the workbook and all I see is "Dummy" sheet with my
message instructing users how to enable macros.


Gord

 

[Martin]

If I followed your comment, that is the problem. The WorkbookOpen event does
not fire. I am using the following code in my WorkBookOpen event:
Application.ScreenUpdating = False
Worksheets("INSTRUCT").Visible = True
Worksheets("MACROWARNING2007").Visible = False
Application.ScreenUpdating = True

It is intended to hide the MacroWarning and display the Instruct sheet if
security is set properly.

With Excel 2007 it appears that the WorkBookOpen event never fires unless
the security setting is set to Enable all macros. (I may have misspoken
earlier.)

Combine that with the way Excel 2007 displays messages (or not) depending on
the macro security setting makes dealing with this more difficult. This is
what I have seen for the various settings:

"Disable all without notification"-- no messaages are displayed indicating that macros will not work. With my code, the MacroWarning should and does display.
"Disable all with notification"--most of the time, security bar at the top of the screen appears. [See note below.]
"Disable all except signed"--this works like the "Disable all without notification".

The odd thing I found with the "Disable all with notification" is that most
of the time the security bar is displayed above the edit bar. Other times, a
security window appears much like the window that was displayed with Excel
2003 to enable macros.

All of these can require different user intervention to enable the macros in
the file. So being able to read the macro security setting would allow the
appropriate instructions to be displayed.

Thanks.

 

[Gord Dibben]

I have no further ideas or suggestions.

I don't know of a way to capture a user's security settings.

And you certainly did "misspoke earlier"<g>


Gord

If I followed your comment, that is the problem. The WorkbookOpen event does
not fire. I am using the following code in my WorkBookOpen event:
Application.ScreenUpdating = False
Worksheets("INSTRUCT").Visible = True
Worksheets("MACROWARNING2007").Visible = False
Application.ScreenUpdating = True

It is intended to hide the MacroWarning and display the Instruct sheet if
security is set properly.

With Excel 2007 it appears that the WorkBookOpen event never fires unless
the security setting is set to Enable all macros. (I may have misspoken
earlier.)

Combine that with the way Excel 2007 displays messages (or not) depending on
the macro security setting makes dealing with this more difficult. This is
what I have seen for the various settings:

"Disable all without notification"-- no messaages are displayed indicating that macros will not work. With my code, the MacroWarning should and does display.
"Disable all with notification"--most of the time, security bar at the top of the screen appears. [See note below.]
"Disable all except signed"--this works like the "Disable all without notification".

The odd thing I found with the "Disable all with notification" is that most
of the time the security bar is displayed above the edit bar. Other times, a
security window appears much like the window that was displayed with Excel
2003 to enable macros.

All of these can require different user intervention to enable the macros in
the file. So being able to read the macro security setting would allow the
appropriate instructions to be displayed.

Thanks.

Martin

I cannot replicate the problem.

I built a workbook in 2007 and added this code to Thisworkbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = True
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub

I then set security to "Disable all macros without notification"

No code runs when I open the workbook and all I see is "Dummy" sheet with my
message instructing users how to enable macros.


Gord