Link to home
Start Free TrialLog in
Avatar of dlmille
dlmilleFlag for United States of America

asked on

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
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

One of the these three parameters has to be true or the sheet will not be protected:

DrawingObjects, Contents, Scenarios

Kevin
The other parameters are all what is allowed given a protected worksheet.

Kevin
Avatar of dlmille

ASKER

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
The default values for the first three parameters is True.

Kevin
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dlmille

ASKER

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