• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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
0
dlmille
Asked:
dlmille
  • 4
  • 2
1 Solution
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now