Macro Dialog Box
 

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

Macro Dialog Box
 

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.

"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

Macro Dialog Box
 

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

Macro Dialog Box
 

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

"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

Macro Dialog Box
 

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" 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

Macro Dialog Box
 

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


On Thu, 5 Mar 2009 03:42:04 -0800, Barb Reinhardt
wrote:

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" 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

Macro Dialog Box
 

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

Macro Dialog Box
 

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