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!

Yep, that's what I posted to you 4 hours ago.

--
Regards
Roger Govier

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

i do not get why you start by

ActiveSheet.Protect

should not you be unprotecting the page before starting the macro?

i tried all the way possible and it will not even allow me to launch the macro (the leap stay in red and tells me there is something wrong :

DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowInsertingRows:=True,

AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True



(i can't put this leap right after ActiveSheet.Protect

because i have a leap for filters.

could you help me please, i am lsoing hair on that one ;)

On Saturday, January 24, 2009 8:18 PM Road wrote:

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!


On Saturday, January 24, 2009 11:52 PM 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!


On Sunday, January 25, 2009 12:37 PM Road 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:


On Sunday, January 25, 2009 1:01 PM Roger Govier wrote:

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
...


On Sunday, January 25, 2009 1:07 PM 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:


On Sunday, January 25, 2009 4:42 PM Road 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:


On Sunday, January 25, 2009 4:59 PM Road wrote:

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:


On Sunday, January 25, 2009 5:53 PM Roger Govier wrote:

Yep, that is what I posted to you 4 hours ago.

--
Regards
Roger Govier


On Tuesday, November 15, 2011 11:10 AM pier myn wrote:

i do not get why you start by

ActiveSheet.Protect

should not you be unprotecting the page before starting the macro?

i tried all the way possible and it will not even allow me to launch the macro (the leap stay in red and tells me there is something wrong :

DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowInsertingRows:=True,

AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True



(i can't put this leap right after ActiveSheet.Protect

because i have a leap for filters.

could you help me please, i am lsoing hair on that one ;)

The reason the lines stay red colored is because it is one line.

You need line-continuation markers....................(_)

DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowInsertingRows:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True


Gord

On Wed, 16 Nov 2011 09:13:37 GMT, pier myn
wrote:

i do not get why you start by

ActiveSheet.Protect

should not you be unprotecting the page before starting the macro?

i tried all the way possible and it will not even allow me to launch the macro (the leap stay in red and tells me there is something wrong :

DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowInsertingRows:=True,

AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True



(i can't put this leap right after ActiveSheet.Protect

because i have a leap for filters.

could you help me please, i am lsoing hair on that one ;)

On Saturday, January 24, 2009 8:18 PM Road wrote:

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!


On Saturday, January 24, 2009 11:52 PM 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!


On Sunday, January 25, 2009 12:37 PM Road 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:


On Sunday, January 25, 2009 1:01 PM Roger Govier wrote:

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
...


On Sunday, January 25, 2009 1:07 PM 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:


On Sunday, January 25, 2009 4:42 PM Road 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:


On Sunday, January 25, 2009 4:59 PM Road wrote:

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:


On Sunday, January 25, 2009 5:53 PM Roger Govier wrote:

Yep, that is what I posted to you 4 hours ago.

--
Regards
Roger Govier


On Tuesday, November 15, 2011 11:10 AM pier myn wrote:

i do not get why you start by

ActiveSheet.Protect

should not you be unprotecting the page before starting the macro?

i tried all the way possible and it will not even allow me to launch the macro (the leap stay in red and tells me there is something wrong :

DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowInsertingRows:=True,

AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True



(i can't put this leap right after ActiveSheet.Protect

because i have a leap for filters.

could you help me please, i am lsoing hair on that one ;)