Good point.

Thanks, Gord

On Thu, 1 Oct 2009 14:32:16 -0700, Raymond W.
wrote:

I hate to butt in here, but I happened across this code and found it more
efficient than the one I used. I have only one problem with it:

ThisWorkbook.Save

That code is only activated if macros are enabled, so by having it in the
code it forces the workbook to save every time it is closed - which could
lead to disaster if a mistake or a test was made on the document. I removed
it for my use, just wanted to thank you for the efficient code here Gord.

"Gord Dibben" wrote:

Same answer as last time.

If security is high, no unsigned code will run so you can't pop up the
message box using code.

If security is Medium, the message box will pop up allowing user to enable
or disable.

If disabled, the workbook still opens but macros won't run.

Generally you have a contingency plan that renders the workbook useless if
users open with macros disabled.

Here is a sample................

Create a sheet named Dummy with a large message typed in the middle.

"You have disabled Macros and this workbook is useless without them. Please
close and re-open with macros enabled"

Then add these two event codes to Thisworkbook module.

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


Gord Dibben MS Excel MVP





On Wed, 4 Mar 2009 16:42:02 -0800, will07
wrote:

Hi, Is there a code available that would bring up the disable/enable macro
security box. I would need the user to enable the Macros to proceed, if the
user did not then the file would not open. I do not want the user to change
the security level, only to enable the macros.

thanks