Protecting sheet through vba while allowing some actions

[robs3131]

Hi,

I've done a lot of searching on this but am stuck - I'm guessing the answer
is simple - basically, I have a large file with 65 sheets -- on 15 of these
sheets, I would like to have them protected, but still allow the user to do
things such as sort, autofilter, format, and a few others.

The issue is that there is a lot of code that needs to be executed on these
sheets when certain buttons are clicked, so I would like to protect the
userinterface only. From what I have found, this can be done using code like
the following - my issue is that I need to allow the user to do more than
what is listed below -- is there a complete list of "Enables" (for lack of a
better term") that can be used? IE - I need to allow the users to "Format
Cells", "Format Columns", and "Edit Objects" -- these are all choices in the
protection pop-up menu when manually setting protection.

I guess the question is how can I set these same parameters using vba?

With Worksheets("Sheet1")
.Enable.Sort
.EnableAutoFilter = True
.Protect UserInterfaceOnly:=True
End With

 

[Rick Rothstein \(MVP - VB\)]

Type "worksheet" (without quote marks)(note there's NO "s" at the end) in a
code window or the Immediate window and, with the text cursor touching the
word anywhere, press F1. When the dialog box appears, pick the first item in
the list... Worksheet (object). That will take you to the help files for the
"Worksheet Object". The available Properties, Methods and Events will be
available via the links at the top of the page.

Rick

 

[robs3131]

Thanks Rick. After looking around in Help for a while, I finally found what
felt like to be the "holy grail" of information I was looking for - all
parameters that can be set when using "Activesheet.protect" For anyone else
looking for this, after getting to "Worksheet Object" in help (as Rick
explains below), follow this path: Worksheet Object - Methods - Protect -
Worksheet Object.

Now the question I have is that it looks like "AllowSorting:=True" only
works if the cells trying to be sorted are unlocked or unprotected...is there
a way to sort protected cells?

Thanks!

 

[Rick Rothstein \(MVP - VB\)]

You can execute Unprotect method of the worksheet in question beforehand and
execute Protect method afterwards. However, you should look the Protect
method up in the help files as there are several optional arguments
available. Also, if there is a password, you will have to know what it is.

Rick