Preventing "Protect Workbook"

[Robert Crandal]

Is there any way to disable the "Protect Workbook"
button in Excel 2007?? Just curious if there any ways
to prevent this option.

The reason I'm asking about this is because my workbook
contains lots of VBA code in which I set a sheet's "visible"
property to "xlVeryHidden" or "xlVisible". So, if someone
protects the workbook, the VBA code in the workbook
will crash if it encounters a line of code that attempts to
change any sheet's "visible" property.

thank u

 

[Per Jessen]

As far as I know you can not prevent protection, but you can test if the
workbook has been protected. It the workbook is protected, prompt user to
unprotect then run the macro again.

See below how to test:

Sub aaa()
If ThisWorkbook.ProtectStructure = True Then
msg = MsgBox("Protected")
Else
msg = MsgBox("Unprotected")
End If
End Sub

Regards,
Per

 

[Paul]

Why not protect the workbook yourself, as part of your code, then you have
direct control over what the user can do.

The trade off is that you'll have to unprotect the workbook each time you
want to make your visibility changes, but this should make the whole process
much more robust.