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


--
Robert

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" wrote in message
...
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


--
Robert

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!

--
Robert


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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


--
Robert

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


"robs3131" wrote in message
...
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!

--
Robert


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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


--
Robert