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 said:
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