How can I suppress the enable macros and save changes messages?

[Ron in Walnut Creek]

I have a multi-worksheet "Dashboard" that is viewed by numerous people. I
want to prevent changes (protect) and suppress the "Enable Macros" message
(default to YES) when opening the workbook and the "Save Changes" message
(default to NO) when closing the workbook.

 

[Gord Dibben]

To suppress the "enable macros" you either set security to low.......not a
good idea.....or digitally sign the workbook.

To close without saving or allowing saving.

In Thisworkbook module paste these two events.

Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "You are not allowed to save this workbook! Please close only"
Cancel = True
End Sub

To save the workbook first time with code not firing, open Immediate Window
and type Application.EnableEvents = False.

Hit Enter key.

Save workbook.

In Immediate Window change the False to True

Close workbook to save with no changes or try to save and get the "You sre
not allowed" message.



Gord Dibben MS Excel MVP



On Thu, 29 Oct 2009 11:12:01 -0700, Ron in Walnut Creek <Ron in Walnut

 

[Gary Brown]

Ron,
To suppress the 'Enable Macros' message, several conditios must be met.
1) The user's security level must be at Medium or lower.
2) You need to add a certificate of authenticity to the workbook.

To accomplish #2,
(i) find the selfcert.exe program on your hard drive.
(ii) follow it's directions to create your certification.
(iii) add the certification to the workbook by (a) opening the workbook and
getting in the Visual Basic Editor [Tools > Macro > Visual Basic Editor], (b)
select Tools > Digital Signature and then 'Choose' the certificate you just
created. (c) select OK.
(iv) save the workbook.

3) The first time the user opens the workbook after you have added a
certification, it will ask them if your certification can be trusted. If
they select YES/OK, they will not be asked to enable macros again when
opening that workbook.

To suppress the save changes message when closing the workbook, you need
to add the following code to the ThisWorkbook module...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

 

[Tom Hutchins]

To prevent changes, change the file to Read Only in Windows Explorer (select
the file, right-click, select Properties, check Read Only, click OK). To
prevent the "Save Changes" message when closing the workbook, put the
following event code in the ThisWorkbook module of the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

You can't suppress/change/bypass the "Enable Macros" dialog when the
workbook is opened. If you could, what would be the use of security settings
to disable macros? If the users of the workbook change their macro security
settings to Low, they won't get the "Enable Macros" dialog, but this is not
recommended.

Provided the security level is set to only allow "signed" macros (with a
certificate) on all machines involved:
Attach a personal certificate to your workbook (digitally sign the VBA
project. You can create your own digital signature using SelfCert, at
Application.Path & "\SelfCert.exe". )
Send your workbook to your users.
When they first open the workbook, after choosing "view the certificate" or
"view details", they will have the choice to install the attached
certificate, once done, they will be able to allow all macros from this
certicate, once and for all.

Hope this helps,

Hutch

 

[Gary Brown]

sorry, I misspoke. the code is...
Application.ActiveWorkbook.Close SaveChanges:=False
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

Ron,
To suppress the 'Enable Macros' message, several conditios must be met.
1) The user's security level must be at Medium or lower.
2) You need to add a certificate of authenticity to the workbook.

To accomplish #2,
(i) find the selfcert.exe program on your hard drive.
(ii) follow it's directions to create your certification.
(iii) add the certification to the workbook by (a) opening the workbook and
getting in the Visual Basic Editor [Tools > Macro > Visual Basic Editor], (b)
select Tools > Digital Signature and then 'Choose' the certificate you just
created. (c) select OK.
(iv) save the workbook.

3) The first time the user opens the workbook after you have added a
certification, it will ask them if your certification can be trusted. If
they select YES/OK, they will not be asked to enable macros again when
opening that workbook.

To suppress the save changes message when closing the workbook, you need
to add the following code to the ThisWorkbook module...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

I have a multi-worksheet "Dashboard" that is viewed by numerous people. I
want to prevent changes (protect) and suppress the "Enable Macros" message
(default to YES) when opening the workbook and the "Save Changes" message
(default to NO) when closing the workbook.