Why does Sheet1.Protect not work properly when including more than a few of the parameters?

I have a utility I've almost completed as part of a question I've answered on E-E.  This utility allows one to specify the Protect options on each sheet in the workbook.

My command looks like:

dim mySheet as Worksheet

        'Now protect the sheet with appropriate options
        mySheet.Protect Password:=mypwd, DrawingObjects:=bDrawingObjects, Contents:=bContents, Scenarios:=bScenarios, UserInterfaceOnly:=bUserInterfaceOnly, _
            AllowFormattingCells:=bAllowFormattingCells, AllowFormattingColumns:=bAllowFormattingColumns, AllowFormattingRows:=bAllowFormattingRows, _
            AllowInsertingColumns:=bAllowInsertingColumns, AllowInsertingRows:=bAllowInsertingRows, AllowInsertingHyperlinks:=bAllowInsertingHyperlinks, _
            AllowDeletingColumns:=bAllowDeletingColumns, AllowDeletingRows:=bAllowDeletingRows, AllowSorting:=bAllowSorting, AllowFiltering:=bAllowFiltering, _
            AllowUsingPivotTables:=bAllowUsingPivotTables

Where each of the parameters get a TRUE/FALSE, based on user selection.  When I test this the sheet does NOT get protected.

The sheet does NOT get protected when I try:

mySheet.Protect mypwd, False, False, False, False, False, False, False, False, False, False, False, False, False, False, False

Why is this?  I can protect it if I only specify a couple of the parameters after the password, but not ALL of them, or even more than a few...

Help!

Thanks!

Dave
LVL 42
dlmilleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
One of the these three parameters has to be true or the sheet will not be protected:

DrawingObjects, Contents, Scenarios

Kevin
0
zorvek (Kevin Jones)ConsultantCommented:
The other parameters are all what is allowed given a protected worksheet.

Kevin
0
dlmilleAuthor Commented:
So

mySheet.Protect Password:=myPwd

with no other parameters then implies one of these parameters you mention are TRUE?  Is it correct to assume if a parameter is omitted, its value is FALSE?  If that's the case, how is it that this command works?

Thanks,

Dave
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

zorvek (Kevin Jones)ConsultantCommented:
The default values for the first three parameters is True.

Kevin
0
zorvek (Kevin Jones)ConsultantCommented:
From Excel help:

Password Optional Variant A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook.
DrawingObjects Optional Variant True to protect shapes. The default value is True.
Contents Optional Variant True to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the locked cells. The default value is True.
Scenarios Optional Variant True to protect scenarios. This argument is valid only for worksheets. The default value is True.
UserInterfaceOnly Optional Variant True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.
AllowFormattingCells Optional Variant True allows the user to format any cell on a protected worksheet. The default value is False.
AllowFormattingColumns Optional Variant True allows the user to format any column on a protected worksheet. The default value is False.
AllowFormattingRows Optional Variant True allows the user to format any row on a protected. The default value is False.
AllowInsertingColumns Optional Variant True allows the user to insert columns on the protected worksheet. The default value is False.
AllowInsertingRows Optional Variant True allows the user to insert rows on the protected worksheet. The default value is False.
AllowInsertingHyperlinks Optional Variant True allows the user to insert hyperlinks on the worksheet. The default value is False.
AllowDeletingColumns Optional Variant True allows the user to delete columns on the protected worksheet, where every cell in the column to be deleted is unlocked. The default value is False.
AllowDeletingRows Optional Variant True allows the user to delete rows on the protected worksheet, where every cell in the row to be deleted is unlocked. The default value is False.
AllowSorting Optional Variant True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False.
AllowFiltering Optional Variant True allows the user to set filters on the protected worksheet. Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter. The default value is False.
AllowUsingPivotTables Optional Variant True allows the user to use pivot table reports on the protected worksheet. The default value is False.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dlmilleAuthor Commented:
Ouch - I should have read that much more carefully - I got all the parameters but didn't look at defaults - I assumed and you know what that means.  Fortunately, you helped me avoid beating my head against the wall too much.

Much appreciated!

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.