Notify user macros are disabled.
Insert a new worksheet. Name it "Dummy".
In large bold font on this worksheet type
"Macros have been disabled, rendering this workbook useless. Please close
and re-open with macros enabled"
Add this code to Thisworkbook module.
Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = xlSheetVisible
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub
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
Gord Dibben MS Excel MVP
On Fri, 12 Mar 2010 10:39:01 -0800, DocBrown
wrote:
I want to try and notify the user that macros are disabled. The problem comes
when the user overlooks the Security warning bar at the top of Excel 2007 and
doesn't enable the marcros as needed. I can't seem to figure out the last
details.
Our IT requires that we have security set to disable macros with
notification. I'm still investigating getting IT to set the security to trust
macros in a defined location for our use.
My method of warning the user is to have a cell on the worksheets say macros
are disabled and to clear the message when the file is opened and set it
before the file is closed.
I'm trying to use the Auto_open and BeforeClose events, but there are still
holes where the logic breaks down. Especially dealing with the "Do you want
to save changes... " dialog.
Does anyone have logic to make this work?
Thanks,
|