Macro Dialog Box

[will07]

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

 

[Barb Reinhardt]

In 2003, go to Tools / Macro / Security and adjust the security level as
needed. What you may need is to sign the code and ensure that your users
have the signature on their machine. At that point, they don't need to
select to enable macros because it will be done automatically.

 

[Gord Dibben]

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

 

[will07]

Thank you for the Code, It worked very well and ensures that the macros are
enabled as i wanted.

 

[Barb Reinhardt]

Gord,

This still doesn't "force" the macros to be enabled each time. THe user can
still choose to disable them and the macros won't run. The only foolproof
way I can think of is to put a digital certificate of some kind on the
workbook and ensure that the users have that cert and they don't ever get the
"enable macros" query again.

Barb

 

[Gord Dibben]

It does not force users to enable macros but if they do disable, they will
not be able to use the workbook.

I suppose they could then go do something else.........play Internet Poker?

The boss might not appreciate that<g>

But, I agree that a signed workbook is the best way to go.


Gord

 

[Raymond W.]

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]

Good point.

Thanks, Gord

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.