Prohibiting the File from Opening if Macros are Disabled?

[Hamov]

I have a file that contains Macros that I want to share with others. The only problem I have is that users have the right to enable or disable macros. If they disable the macro - some of the security functions could be lost.

Is there a way to create a macro that will not allow the file to be opened if they choose to Disable the Macros.

Could this be done/Anyone have any suggestions?

 

[chrish72]

That happens because your code cannot change the sheets' properties if you have your workbook protected. To avoid that error try to write the following code:

Code to the opening of the file

Dim i As Integer
Dim pass, page As String

pass = "the password you've set for the workbook protection"
page = "the name of your warning page"

Application.EnableCancelKey = xlDisabled
ActiveWorkbook.Unprotect Password:=pass
For i = 1 To ThisWorkbook.Worksheets.Count
' Skip empty sheets And hidden sheets
If ThisWorkbook.Worksheets(i).Name <> page Then
If ThisWorkbook.Worksheets(i).Visible = xlVeryHidden Or ThisWorkbook.Worksheets(i).Visible = False Then
ThisWorkbook.Worksheets(i).Visible = True
End If
End If
Next i
ActiveWorkbook.Protect Password:=pass
Application.EnableCancelKey = xlenabled

End Sub


Code to the closing of the file



Dim i As Integer
Dim pass, page As String
pass = "the password you've set for the workbook protection"
page = "the name of your warning page"

Application.EnableCancelKey = xlDisabled
ActiveWorkbook.Unprotect Password:=palavra_chave
For i = 1 To ThisWorkbook.Worksheets.Count
' Skip empty sheets And hidden sheets
If ThisWorkbook.Worksheets(i).Name <> page Then
If ThisWorkbook.Worksheets(i).Visible = True Then
ThisWorkbook.Worksheets(i).Visible = xlVeryHidden
End If
End If
Next i
ActiveWorkbook.Protect Password:=pass
Application.EnableCancelKey = xlenabled


End Sub

I've already included in the code written above two lines to disable the "cancel key" aka "ESC key" during the running of the macros to avoid people stoping the macro in the middle because during the macro the workbook gets temporarilly unprotected - the macro unprotects the workbook in the beginning in order to be able to change the properties of the sheets and then sets the password again at the end of the macro, that is why you have to disable the cancel option during the macro.

Hope this helps...

Just to tease you guys, this will not stop people from messing with your files.

I've been working on the code to prevent any change to the file and to only show the sheets I want and still haven't find the answer to solve all the little bugs... there are still ways to "crack" my protection...

I realize that you and I have replied in a very old thread, but I had some questions regarding this method...

1. When I save the final iteration that I will deploy to the end users, will the sheet with "you need macros enabled" be the only sheet visible and all others be =xlsheetveryhidden or vice versa? I only ask because I could only find this blurb on another site with similar code and it seems to be the opposite of what should be (but maybe I am not thinking of it correctly):

When deploying your workbook, the last saved version, the one that is distributed to the end users, should have:

• The Introduction sheet Hidden (xlSheetVeryHidden)
• All sheets that should be visible to the user when macros are enabled visible (xlSheetVisible)

2. If my workbook normally opens with a userform and the user has macros disabled, obviously the userform will not load, but if I have this code to show the "you need macros enabled" sheet and they are set up to disable macros WITH notification and they click the "Enable Macros..." button that Excel supplies them...will that fire the Workbook_Open() macro at that time and should I put the Load UserForm and UserForm.Show code at the end of the above code?

I hope my explanations and questions are clear enough, but let me know if I need to clarify at all.

Regards,
Chris

 

[gnrendeiro]

1. When I save the final iteration that I will deploy to the end users, will the sheet with "you need macros enabled" be the only sheet visible and all others be =xlsheetveryhidden or vice versa? I only ask because I could only find this blurb on another site with similar code and it seems to be the opposite of what should be (but maybe I am not thinking of it correctly):

The code I wrote was to hide all the sheets at the closing of the file and unhide them at the oppening, this means that if you don't have your macros enabled your file will open with all the sheets hidden.

2. If my workbook normally opens with a userform and the user has macros disabled, obviously the userform will not load, but if I have this code to show the "you need macros enabled" sheet and they are set up to disable macros WITH notification and they click the "Enable Macros..." button that Excel supplies them...will that fire the Workbook_Open() macro at that time and should I put the Load UserForm and UserForm.Show code at the end of the above code?

I'm not sure if the opening macro will run after the workbook is open when you press the enable macros option. What I do to avoid that is that I place a button on the first page that shows all the hidden pages and that only runs when the macros are enabled. My files don't do nothing at the oppening, just when you press the button on the "Welcome page".

I also think that the code for hidding the pages at the closing of the workbook is not the saffest way. I use it at the saving procedure instead. This way you will be sure that noone can save the file with the sheets unhidden.

 

[chrish72]

The code I wrote was to hide all the sheets at the closing of the file and unhide them at the oppening, this means that if you don't have your macros enabled your file will open with all the sheets hidden.

Wow...quick reply! Thank you!

Ahh...I get it...so when I save the file the important sheets are hidden and the welcome sheet is visible...that way no matter who opens the workbook after that if they have macros disabled they will get the welcome sheet! Thank you for clarifying and making me see why it is done this way!

I'm not sure if the opening macro will run after the workbook is open when you press the enable macros option. What I do to avoid that is that I place a button on the first page that shows all the hidden pages and that only runs when the macros are enabled. My files don't do nothing at the oppening, just when you press the button on the "Welcome page".

I also think that the code for hidding the pages at the closing of the workbook is not the saffest way. I use it at the saving procedure instead. This way you will be sure that noone can save the file with the sheets unhidden.

One more quick question...my userform (modal) has a CLOSE button on it that will save the workbook and close it...should I put the code in the close workbook event or will the save workbook event still be better?

Regards,
Chris

 

[gnrendeiro]

Hi, I'm not that used to work with forms but if you have the option of saving the file, then you should put it on save, otherwise anyone can just save the file when the pages are visible and then when closing just say don't save or make a copy of the file or something. If they do that, they will have the file with the sheets all visible without the macros obligation.

I just organized my the first reply with the opening and closing of the file because that was what people were discussing that they needed. It is always saffer to do it before saving.

 

[chrish72]

Hi, I'm not that used to work with forms but if you have the option of saving the file, then you should put it on save, otherwise anyone can just save the file when the pages are visible and then when closing just say don't save or make a copy of the file or something. If they do that, they will have the file with the sheets all visible without the macros obligation.

I just organized my the first reply with the opening and closing of the file because that was what people were discussing that they needed. It is always saffer to do it before saving.

Thank you very much for your time gnrendeiro!