Prohibiting the File from Opening if Macros are Disabled?

Hamov

Board Regular
Joined
Feb 5, 2003
Messages
98
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?
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Here is how I have gotten past this sort of thing:

Create a new worksheet that has a HUGE message that says, "THIS WORKBOOK REQUIRES MACROS TO BE ENABLED TO WORK PROPERLY. PLEASE EXIT THIS WORKBOOK AND ENABLE MACROS."

Change the Visible property of EVERY worksheet in your workbook (with the exception of the new worksheet you just created) to xlSheetVeryHidden.

In the ThisWorkbook module, add this to your Workbook_Open event:

Sheets("Shee1").Visible = xlSheetVisible
Sheets("Sheet2").Visible = xlSheetVisible
Sheets("New Sheet").Visible = xlSheetVeryHidden

Do the reverse on your Workbook_BeforeClose event:

Sheets("New Sheet").Visible = xlSheetVisible
Sheets("Sheet1").Visible = xlSheetVeryHidden
Sheets("Sheet2").Visible = xlSheetVeryHidden

The only way that the worksheets are visible is if macros are enabled. Otherwise, the user only gets the sheet with the warning message.

Does this help?
 

Hamov

Board Regular
Joined
Feb 5, 2003
Messages
98
Actually - it isn't working - I placed the comment in Worksheet 2 and want Worksheet 1 to be hidden if macros are disabled. Both Private Subroutines are placed in "This Workbook". The error message is "Compile Error: Procedure Declaration Does Not Match Description of Event or Procedure Having the same Name".

Here are the Macro's:

Private Sub Workbook_Open()
'
Sheets("Sheet1").Visible = xlSheetVisible
Sheets("Sheet2").Visible = xlSheetVeryHidden
ActiveWorkbook.ActiveSheet.Range("b15").Value = ActiveWorkbook.FullName & "-" & Dir(ActiveWorkbook.Path, vbVolume)
End Sub

Private Sub Workbook_BeforeClose()
'
Sheets("Sheet1").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("IOEL").Visible = xlSheetVeryHidden
Sheets("10ppm").Visible = xlSheetVeryHidden
Sheets("CA").Visible = xlSheetVeryHidden
Sheets("Micro").Visible = xlSheetVeryHidden
Sheets("Sheet2").Visible = xlSheetVisible
End Sub

Any suggestions why it isn't working? Is it that the macros are a direct contradiction in the same folder?
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
This code will allow you to hide all sheets on closing ... and vica versa , without having to specify each one. Please note that the sheet name for your Warning is called "Warning".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Warning").Visible = xlSheetVisible

' hide all sheet but Warning sheet
For Each sh In Worksheets
If Not sh.Name = "Warning" Then sh.Visible = xlVeryHidden
Next sh
End Sub

Private Sub Workbook_Open()
For Each sh In Worksheets
sh.Visible = xlSheetVisible
Next sh
' hide the warning sheet
Sheets("Warning").Visible = xlVeryHidden
End Sub
 

Hamov

Board Regular
Joined
Feb 5, 2003
Messages
98
Fantastic! It worked!

Thank you for your help! It's greatly appreciated!
 

Hamov

Board Regular
Joined
Feb 5, 2003
Messages
98
After implementing it...it does indeed work ~ BUT...it keeps giving me an ugly

Run Time Error 1004
Unable to Set the Visible Property of the Worksheet Class

The debugger hilites the line:

Sheets("Warning").Visible = xlSheetVisible



Can you offer any suggestions?

This only seems to happy when the Workbook/Worksheet is protected.
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Nimrod's suggestion is the easiest to use. Mine requires you to list each worksheet individually. In either case, one of the things you have to consider is that at least ONE worksheet must be visible at all times. Make sure that you aren't hiding them all and then trying to unhide one. If this bit of advice doesn't help, post your code(s).
 

Hamov

Board Regular
Joined
Feb 5, 2003
Messages
98
The file works great except when I try to use the Protect Workbook function. I then get the Run time error when I try and close/open the file. I believe that my problem has something to do with failing to reset the screen views?

The reason I use the "Protect Workbook" function is because someone can right click the tabs at the bottom and then delete a worksheet - the "Protect Worksheet" function does not protect for this scenario.

Is there a way around this? I was thinking something along the lines of using the Macro Disable button and assigning a number to the softbutton selection, if Disabled then view only one, if enabled then view the others. Would I still need to reset the views in these cases?

Listed below is my code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Warning").Visible = xlSheetVisible

' hide all sheet but Warning sheet
For Each sh In Worksheets
If Not sh.Name = "Warning" Then sh.Visible = xlVeryHidden
Next sh
End Sub

Private Sub Workbook_Open()
For Each sh In Worksheets
sh.Visible = xlSheetVisible
Next sh
' hide the warning sheet
Sheets("Warning").Visible = xlVeryHidden
End Sub


Thanks!

:)
 

gnrendeiro

New Member
Joined
Mar 29, 2012
Messages
4
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... :(
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top