Hello:
I have a spreadsheet (Excel 2003) where the first ten rows need to locked.
The remainder of rows (including an auto-filter header in row 11) are
unlocked. When i password protect the sheet, I check the options boxes that
would allow the user of the protected worksheet to do all of the following:
- Select Unlocked Cells
- SElect Locked Cells
- Format Cells
- Insert Rows
- Delete Rows
- Sort
- Use AutoFilter

This all works great until after I run a macro button. The macro itself
works and is pretty simple, but requires me to insert the following verbiage
at the beginning and end of the macro so that i can unprotect the sheet and
re-protect the sheet in order for the macro to work:
ActiveSheet.Unprotect Password:="StarWars"
ActiveSheet.Protect Password:="StarWars"

The macro performs basic operations involving auto-filter, custom-filters,
sort Ascending and inserting/deleting an X in a particular cell.

When the macro is done, however, it defaults all my protect sheet settings
back to the standard first two options and no longer allows the checkboxes I
had selected. Therefore, not letting the Sort Ascending (For example) be used
anymore.

Suggestions?
Much appreciated!

Look in vba help for the Protect method.
You have to tell Excel "how" you want the sheet protected.
--
Jim Cone
Portland, Oregon USA



"Roady"

wrote in message
Hello:
I have a spreadsheet (Excel 2003) where the first ten rows need to locked.
The remainder of rows (including an auto-filter header in row 11) are
unlocked. When i password protect the sheet, I check the options boxes that
would allow the user of the protected worksheet to do all of the following:
- Select Unlocked Cells
- SElect Locked Cells
- Format Cells
- Insert Rows
- Delete Rows
- Sort
- Use AutoFilter

This all works great until after I run a macro button. The macro itself
works and is pretty simple, but requires me to insert the following verbiage
at the beginning and end of the macro so that i can unprotect the sheet and
re-protect the sheet in order for the macro to work:
ActiveSheet.Unprotect Password:="StarWars"
ActiveSheet.Protect Password:="StarWars"

The macro performs basic operations involving auto-filter, custom-filters,
sort Ascending and inserting/deleting an X in a particular cell.
When the macro is done, however, it defaults all my protect sheet settings
back to the standard first two options and no longer allows the checkboxes I
had selected. Therefore, not letting the Sort Ascending (For example) be used
anymore.
Suggestions?
Much appreciated!

Thanks, Jim, however I did look at that but I think I need some more
direction/guidance.

Can anyone offer a more specific answer to my question? I did look in VBA
help and use some of the coding but it didn't seem to help. For example:
AllowFitering:=Yes

Thanks again, Roady

"Jim Cone" wrote:


Look in vba help for the Protect method.
You have to tell Excel "how" you want the sheet protected.
--
Jim Cone
Portland, Oregon USA



"Roady"

wrote in message
Hello:
I have a spreadsheet (Excel 2003) where the first ten rows need to locked.
The remainder of rows (including an auto-filter header in row 11) are
unlocked. When i password protect the sheet, I check the options boxes that
would allow the user of the protected worksheet to do all of the following:
- Select Unlocked Cells
- SElect Locked Cells
- Format Cells
- Insert Rows
- Delete Rows
- Sort
- Use AutoFilter

This all works great until after I run a macro button. The macro itself
works and is pretty simple, but requires me to insert the following verbiage
at the beginning and end of the macro so that i can unprotect the sheet and
re-protect the sheet in order for the macro to work:
ActiveSheet.Unprotect Password:="StarWars"
ActiveSheet.Protect Password:="StarWars"

The macro performs basic operations involving auto-filter, custom-filters,
sort Ascending and inserting/deleting an X in a particular cell.
When the macro is done, however, it defaults all my protect sheet settings
back to the standard first two options and no longer allows the checkboxes I
had selected. Therefore, not letting the Sort Ascending (For example) be used
anymore.
Suggestions?
Much appreciated!

Hi

Sub ProtectSheet()

ActiveSheet.Protect Password:="StarWars", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingCells:=True, _
AllowInsertingRows:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True

End Sub

Sub UnprotectSheet()
ActiveSheet.Unprotect Password:="StarWars"
End Sub


--
Regards
Roger Govier

"Roady" wrote in message
...
Thanks, Jim, however I did look at that but I think I need some more
direction/guidance.

Can anyone offer a more specific answer to my question? I did look in VBA
help and use some of the coding but it didn't seem to help. For example:
AllowFitering:=Yes

Thanks again, Roady

"Jim Cone" wrote:


Look in vba help for the Protect method.
You have to tell Excel "how" you want the sheet protected.
--
Jim Cone
Portland, Oregon USA



"Roady"

wrote in message
Hello:
I have a spreadsheet (Excel 2003) where the first ten rows need to
locked.
The remainder of rows (including an auto-filter header in row 11) are
unlocked. When i password protect the sheet, I check the options boxes
that
would allow the user of the protected worksheet to do all of the
following:
- Select Unlocked Cells
- SElect Locked Cells
- Format Cells
- Insert Rows
- Delete Rows
- Sort
- Use AutoFilter

This all works great until after I run a macro button. The macro itself
works and is pretty simple, but requires me to insert the following
verbiage
at the beginning and end of the macro so that i can unprotect the sheet
and
re-protect the sheet in order for the macro to work:
ActiveSheet.Unprotect Password:="StarWars"
ActiveSheet.Protect Password:="StarWars"

The macro performs basic operations involving auto-filter,
custom-filters,
sort Ascending and inserting/deleting an X in a particular cell.
When the macro is done, however, it defaults all my protect sheet
settings
back to the standard first two options and no longer allows the
checkboxes I
had selected. Therefore, not letting the Sort Ascending (For example) be
used
anymore.
Suggestions?
Much appreciated!

Use the macro recorder to see what terms to use.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingColumns:=True,
AllowSorting:= _
True, AllowFiltering:=True

And stuff like that there.


Gord Dibben MS Excel MVP


On Sun, 25 Jan 2009 09:37:01 -0800, Roady
wrote:

Thanks, Jim, however I did look at that but I think I need some more
direction/guidance.

Can anyone offer a more specific answer to my question? I did look in VBA
help and use some of the coding but it didn't seem to help. For example:
AllowFitering:=Yes

Thanks again, Roady

"Jim Cone" wrote:


Look in vba help for the Protect method.
You have to tell Excel "how" you want the sheet protected.
--
Jim Cone
Portland, Oregon USA



"Roady"

wrote in message
Hello:
I have a spreadsheet (Excel 2003) where the first ten rows need to locked.
The remainder of rows (including an auto-filter header in row 11) are
unlocked. When i password protect the sheet, I check the options boxes that
would allow the user of the protected worksheet to do all of the following:
- Select Unlocked Cells
- SElect Locked Cells
- Format Cells
- Insert Rows
- Delete Rows
- Sort
- Use AutoFilter

This all works great until after I run a macro button. The macro itself
works and is pretty simple, but requires me to insert the following verbiage
at the beginning and end of the macro so that i can unprotect the sheet and
re-protect the sheet in order for the macro to work:
ActiveSheet.Unprotect Password:="StarWars"
ActiveSheet.Protect Password:="StarWars"

The macro performs basic operations involving auto-filter, custom-filters,
sort Ascending and inserting/deleting an X in a particular cell.
When the macro is done, however, it defaults all my protect sheet settings
back to the standard first two options and no longer allows the checkboxes I
had selected. Therefore, not letting the Sort Ascending (For example) be used
anymore.
Suggestions?
Much appreciated!

I think I am getting closer but not quite there.

It will allow me to do those things but not at the same time as protecting
with a password. This is what I copied/pasted from recording it and it did
not capture the password part of it.
See below:

Sub Refresh()
ActiveSheet.Unprotect Password:="StarWars"
'
(--Main Body of Macro goes here--)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingRows:=True,
AllowDeletingRows _
:=True, AllowSorting:=True, AllowFiltering:=True
End Sub

Then, if I try adding in the following verbiage either before or after the
above, it protects it with the password but does not allow the additional
functionality as requested above once it is protected again:
ActiveSheet.Protect Password:="StarWars"

Suggestions for addressing both protecting w/password and allowing all those
checkbox options? thank you again. Roady



"Gord Dibben" wrote:

Use the macro recorder to see what terms to use.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingColumns:=True,
AllowSorting:= _
True, AllowFiltering:=True

And stuff like that there.


Gord Dibben MS Excel MVP


On Sun, 25 Jan 2009 09:37:01 -0800, Roady
wrote:

Thanks, Jim, however I did look at that but I think I need some more
direction/guidance.

Can anyone offer a more specific answer to my question? I did look in VBA
help and use some of the coding but it didn't seem to help. For example:
AllowFitering:=Yes

Thanks again, Roady

"Jim Cone" wrote:


Look in vba help for the Protect method.
You have to tell Excel "how" you want the sheet protected.
--
Jim Cone
Portland, Oregon USA



"Roady"

wrote in message
Hello:
I have a spreadsheet (Excel 2003) where the first ten rows need to locked.
The remainder of rows (including an auto-filter header in row 11) are
unlocked. When i password protect the sheet, I check the options boxes that
would allow the user of the protected worksheet to do all of the following:
- Select Unlocked Cells
- SElect Locked Cells
- Format Cells
- Insert Rows
- Delete Rows
- Sort
- Use AutoFilter

This all works great until after I run a macro button. The macro itself
works and is pretty simple, but requires me to insert the following verbiage
at the beginning and end of the macro so that i can unprotect the sheet and
re-protect the sheet in order for the macro to work:
ActiveSheet.Unprotect Password:="StarWars"
ActiveSheet.Protect Password:="StarWars"

The macro performs basic operations involving auto-filter, custom-filters,
sort Ascending and inserting/deleting an X in a particular cell.
When the macro is done, however, it defaults all my protect sheet settings
back to the standard first two options and no longer allows the checkboxes I
had selected. Therefore, not letting the Sort Ascending (For example) be used
anymore.
Suggestions?
Much appreciated!

Never mind- I figured it out- here it is for anyone who is struggling with
this:

ActiveSheet.Protect Password:="StarWars", DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingRows:=True,
AllowDeletingRows _
:=True, AllowSorting:=True, AllowFiltering:=True

"Roady" wrote:

I think I am getting closer but not quite there.

It will allow me to do those things but not at the same time as protecting
with a password. This is what I copied/pasted from recording it and it did
not capture the password part of it.
See below:

Sub Refresh()
ActiveSheet.Unprotect Password:="StarWars"
'
(--Main Body of Macro goes here--)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingRows:=True,
AllowDeletingRows _
:=True, AllowSorting:=True, AllowFiltering:=True
End Sub

Then, if I try adding in the following verbiage either before or after the
above, it protects it with the password but does not allow the additional
functionality as requested above once it is protected again:
ActiveSheet.Protect Password:="StarWars"

Suggestions for addressing both protecting w/password and allowing all those
checkbox options? thank you again. Roady



"Gord Dibben" wrote:

Use the macro recorder to see what terms to use.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingColumns:=True,
AllowSorting:= _
True, AllowFiltering:=True

And stuff like that there.


Gord Dibben MS Excel MVP


On Sun, 25 Jan 2009 09:37:01 -0800, Roady
wrote:

Thanks, Jim, however I did look at that but I think I need some more
direction/guidance.

Can anyone offer a more specific answer to my question? I did look in VBA
help and use some of the coding but it didn't seem to help. For example:
AllowFitering:=Yes

Thanks again, Roady

"Jim Cone" wrote:


Look in vba help for the Protect method.
You have to tell Excel "how" you want the sheet protected.
--
Jim Cone
Portland, Oregon USA



"Roady"

wrote in message
Hello:
I have a spreadsheet (Excel 2003) where the first ten rows need to locked.
The remainder of rows (including an auto-filter header in row 11) are
unlocked. When i password protect the sheet, I check the options boxes that
would allow the user of the protected worksheet to do all of the following:
- Select Unlocked Cells
- SElect Locked Cells
- Format Cells
- Insert Rows
- Delete Rows
- Sort
- Use AutoFilter

This all works great until after I run a macro button. The macro itself
works and is pretty simple, but requires me to insert the following verbiage
at the beginning and end of the macro so that i can unprotect the sheet and
re-protect the sheet in order for the macro to work:
ActiveSheet.Unprotect Password:="StarWars"
ActiveSheet.Protect Password:="StarWars"

The macro performs basic operations involving auto-filter, custom-filters,
sort Ascending and inserting/deleting an X in a particular cell.
When the macro is done, however, it defaults all my protect sheet settings
back to the standard first two options and no longer allows the checkboxes I
had selected. Therefore, not letting the Sort Ascending (For example) be used
anymore.
Suggestions?
Much appreciated!