Notify user macros are disabled.

[DocBrown]

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,

 

[Barb Reinhardt]

We've added digital signatures to workbooks with macros and IT has pushed the
digital signature out to everyone's computer. I don't know how it was all
done, but it's very handy when we have a new macro. All we need to do is
sign it with the corporate signature.

 

[Gord Dibben]

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

 

[DocBrown]

I do really appreciate the answers. Unfortunetly, there are several reasons
it won't work in my situation. The first is that the workbook is always saved
and I want the users to be able to respond to the 'Do you want to save
changes..' dialog with 'No'. It also doesn't seem to allow Excel to handle
the 'Cancel' response the way I'd like.

But I did come up with a way that seems to work. I include it here so other
may use it.

First I create a macro:

Public Function TestMacros() As Boolean
TestMacros = True
End Function

....Bear with me...

Then I put the message on the WS that I want in Red Bold (your message may
very...):

The automatic workbook functions are disabled.
Make sure that you enable Macros.

Then for the cell with the message I create Conditional format that will set
the font color to the background:

=TestMacros()

The message cell is in an area that is protected. If macros are disable,
then the formula returns error and the format is not changed.