Preventing "Protect Workbook"

R

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
 
P

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
 
P

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top