ProtectSheet Options are un-checking after macro

  • Thread starter Thread starter Roady
  • Start date Start date
R

Roady

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"
<[email protected]>
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
 
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
 
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
 
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
 
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
 
After serious thinking 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
Click to expand...

You can't turn on sheet protection if a sheet is already protected! You
MUST unprotect the sheet, THEN reapply protection. See an example under
the thread "Protecting sheet that has radio buttons" submitted on Nov
12th to this forum.

Also, you don't have to unprotect a sheet to programmatically change
locked cells if you include the 'UserInterfaceOnly:=True' parameter
when you apply protection. Note, however, that this parameter is only
effective during runtime and so protection must be 'reset' each time
the file is opened. Here's the sample code...

Sub ResetWksProtection(Optional Wks As Worksheet)
' Simplistic method for setting/resetting sheet protection so code can
make changes without restriction.
' If a sheet is already protected we MUST unprotect it before resetting
protection.
' Some protection parameters do not persist after closing and so must
be reset when the file reopens.
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD
Wks.Protect PWRD, DrawingObjects:=True, Contents:=True,
Scenarios:=True, UserInterfaceOnly:=True
End Sub

Sub ProtectAllSheets()
Dim Wks As Worksheet
For Each Wks In ThisWorkbook.Worksheets: ResetWksProtection Wks: Next
'wks
End Sub

At startup, (in an Auto_Open sub or Workbook_Open event), make a call
to ProtectAllSheets. You can call this procedure when the workbook
using it opens, OR you can call it when your code opens a workbook that
you want to reset sheet protection for. You can protect a specific
sheet by passing a reference to it, as shown.

HTH
 
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
 
Back
Top