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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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!

:)
 
Upvote 0
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... :(
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top