Deny file access if user declines macros?

S

slingsh0t

I periodically issue a file that contains macros to users. For ease of
functionality, the users should accept the macros (as when their
security level is set to medium).

Is it possible to deny a user access to the file if he answers "no, do
not allow macros" to the security question?

Thanks
 
G

Gregg Riemer

Before closing the workbook, you can set the visible property of all the
worksheets to very hidden and then protect the workbook with a password.

Then have an auto exec macro that makes all the sheets visible when the
workbook is opened. The auto exec macro won't execute unless the user
enables macros and they won't know the password to unprotect the workbook.

Try something along the lines of the following:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Visible = False
Sheets("Sheet1").Visible = xlVeryHidden
ActiveWorkbook.Protect Password:="enter a password here"
End Sub

Private Sub Workbook_Open()
ActiveWorkbook.Unprotect Password:="enter a password here"
Sheets("Sheet1").Visible = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Visible = False
Sheets("Sheet1").Visible = xlVeryHidden
ActiveWorkbook.Protect Password:="susan"
End Sub
 
J

JE McGimpsey

This will work fine for the incurious user. Just be aware that anyone
with enough gumption to find these newsgroups can find ways to bypass
that level of protection.
 
S

slingsh0t

Thanks, Gregg and JE. As I thought about it over the weekend the
solution I came up with was to set a boolean value to FALSE on file
exit, and use a startup macro to set that value to TRUE when they
accepted macros. Then tag certain critical formulas to operate only
when that value is TRUE. But I like the xlVeryHidden idea- thanks.

Is there- or did there use to be- a function called xlHopelesslyHidden?
This is ringing a bell for some reason.
 
Top