Modifying a protect unprotect macro

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

All I'm hoping someone can help with placing a Yes / No popup in an
existing macro.

I use this macro to protect / unprotect my worksheets.

Sub Protect_Unprotect()


Const PWORD As String = "password"
Dim wkSht As Worksheet
Dim statStr As String

Application.ScreenUpdating = False

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFiltering:=True ', _
AllowFormattingCells:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
Userinterfaceonly:=True, _
AllowDeletingColumns:=True, _
AllowUsingPivotTables:=True
statStr = statStr & ": Protected"
End If
End With
Next wkSht

Application.ScreenUpdating = True

MsgBox Mid(statStr, 2)

End Sub

My problem is that for some workbooks i need all of the qualifying
permissions listed here to be reinstated on protection. In others , I
don't need them. In these the limited basic permission to select locked
/ unlocked in needed.

What I'm trying to put in place is a Yes / No popup asking 'All
qualifying permissions?'. If the answer is 'Yes' then it proceeds
through the macro. If the answer is 'No' then it jumps over the
permissions listed in the macro and protects with the basic select
locked / unlocked cells.

Can someone help with this? I did try some coding of my own , but am
struggling to find the right place in the code to place the popup VBA.

Grateful for any help.
 
Here's how I would do it...

Sub Protect_Unprotect()
Const PWORD As String = "password" '//this should be module level
Dim wkSht As Worksheet, statStr As String, vAns As Variant

Application.ScreenUpdating = False
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & "Sheet " & .Name & vbNewLine
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
vAns = MsgBox("All qualifying permissions?", vbYesNo,
"Protecting Sheets")
If vAns = vbYes Then
wkSht.Protect Password:=PWORD, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFiltering:=True ', _
AllowFormattingCells:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
Userinterfaceonly:=True, _
AllowDeletingColumns:=True, _
AllowUsingPivotTables:=True
Else
.EnableSelection = xlUnlockedCells
End If
statStr = statStr & ": Protected"
End If
End With
Next
Application.ScreenUpdating = True
MsgBox statStr
End Sub

P.S.: I see your permissions list is commented out same as my sample.
Did you mean to leave that this way?
 
Hi Garry

OK Thanks for that. I'm grateful for your help.

It's working fine , but puts up a popup for each sheet in the workbook
when protecting. This means a key press for each worksheet. Ideally it
would protect all of them one after another on the first single
response. Is this possible?

Also , it seems to put up the 'protect ' popup on the 'unprotect' cycle
, when of course it's not necessary.

Yes , it's commented out in the same way as you suggested earlier
because it's related to preserving the permissions in the calendar
program which are not required for other workbooks I have.


Best Wishes.
 
Colin Hayes explained :
Hi Garry

OK Thanks for that. I'm grateful for your help.

It's working fine , but puts up a popup for each sheet in the workbook when
protecting. This means a key press for each worksheet. Ideally it would
protect all of them one after another on the first single response. Is this
possible?

Also , it seems to put up the 'protect ' popup on the 'unprotect' cycle ,
when of course it's not necessary.

Yes , it's commented out in the same way as you suggested earlier because
it's related to preserving the permissions in the calendar program which are
not required for other workbooks I have.
Click to expand...

Colin,
I don't see how the 'popup' can display before the Else part, meaning
the sheet is unprotected and therefore needs to be protected.

Also, I don't understand why your protect/unprotect procedure would
deliberately iterate the sheets. I do that before calling my procedure
so I have the flexibility I need to be able to do single sheets, and/or
specify the workbook. Please review my code sample for using
wksProtect. I recommend going with the 'Wks As Worksheet' version.

Also, I didn't include unprotect code deliberately because that's
always a one-liner: 'wks.Unprotect PWORD'. Works whether a PWORD
exists or not (ergo, no error is raised if no password is used).

Finally, I don't give users the option to choose permissions for sheets
that belong to a project. Might be okay to do if a general utility,
though!
 
Colin,
I don't see how the 'popup' can display before the Else part, meaning the sheet is
unprotected and
therefore needs to be protected.

Also, I don't understand why your protect/unprotect procedure would deliberately
iterate the sheets.
I do that before calling my procedure so I have the flexibility I need to be able to do
single sheets,
and/or specify the workbook. Please review my code sample for using
wksProtect. I recommend
going with the 'Wks As Worksheet' version.

Also, I didn't include unprotect code deliberately because that's always a one-liner:
'wks.Unprotect
PWORD'. Works whether a PWORD exists or not (ergo, no error is raised if no
password is used).

Finally, I don't give users the option to choose permissions for sheets that belong
to a project. Might
be okay to do if a general utility, though!
Click to expand...


Hi Garry

OK Thanks for your help.

I find it's a lot of identical key presses to give separate and
individual permissions to each sheet one at a time , when my intention
always to allow the same permissions to all. It would be much easier to
have a single key press on the popup to protect all the sheets at one
go. It kind of undermines the facility of this type of project. Never
mind - I dare I could get used to it

^_^

Best Wishes


Colin
 
Colin,
Removing the iteration of sheets from your protect/unprotect procedure
will allow you the flexibility to ask the Q 1x before you call
protect/unprotect. The purpose of your proc (as I see it) is to have 1
proc to handle all protection needs per project. Iterating the wks
collection doesn't belong in there! It belongs in a front-loader sub
where you can determine what users want beforehand without compromising
how you want the protect/unprotect proc to work. I'm talking about
splitting the app logic from the process so you know how to execute the
process based on user input.

What I also see you trying to implement in your proc is 'context
sensitivity' about whether a sheet is already protected or not, AND
proceed according to that. Wrong place to do this<IMO>!

I advise using a front-loader sub to work this stuff out BUT not
context stuff. I leave that to menus like Excel does. (Ever notice the
menu available for a protected sheet is "Unprotect Sheet...", and vice
versa for an unprotected sheet. This is context-sensitive 'app logic'.
I'll try to elaborate...

==============================
Protect sheets:
This is a deliberate action performed by either the user OR your app,
to protect content from being changed by others OR control the
workspace environment.
--
Since XL10 we've been given 'permissions' options that we can enforce
DURING THE PROTECT PROCESS. These are a bit harder to code for if
trying to dupe what the Protection dialog does in VBA. It might be
easier to use a multi-select listbox and pass the choices to fully
enable (not commented out) options in the proc. Load the choices into a
public boolean array and read them from the protect proc:

With Wks
If Val(Application.VERSION) >= 10 Then
'Copy/paste the desired parameters above the commented line.
.Protect Password:=PWRD, _
DrawingObjects:=gbaProtect(10), _
Contents:=gbaProtect(11), _
Scenarios:=gbaProtect(12), _
Userinterfaceonly:=gbaProtect(13), _

AllowFiltering:=gbaProtect(0), _
AllowFormattingColumns:=gbaProtect(1), _
AllowFormattingRows:=gbaProtect(2), _
AllowFormattingCells:=gbaProtect(3), _
AllowDeletingColumns:=gbaProtect(4), _
AllowDeletingRows:=gbaProtect(5), _
AllowInsertingColumns:=gbaProtect(6), _
AllowInsertingHyperlinks:=gbaProtect(7), _
AllowInsertingRows:=gbaProtect(8), _
AllowUsingPivotTables:=gbaProtect(9)
Else
.Protect Password:=PWRD, _
DrawingObjects:=gbaProtect(10), _
Contents:=gbaProtect(11), _
Scenarios:=gbaProtect(12), _
Userinterfaceonly:=gbaProtect(13), _
End If
End With
--

Unprotect sheets:
This is also a deliberate action performed by either the user OR your
app, to protect content from being changed by others. It's basically a
one-liner: <wks>.Unprotect Password:="pwrd". No need to have it inside
a protect proc as a convenience because that way you're letting the
proc decide what to do, removing control of the workspace environment
from your app or the user.
==============================

Even if you're trying to 'effect' a toggle methodology I'd still
separate the two and do the 'toggling' in a front-loader sub. I think
you'll find this will give you better (ie more reliable) coding in the
end. Here, then, you can determine whether to unprotect based on the
caption of CommandBars.ActionControl (assumes an events handler is in
place to set caption) OR prompt the user to select protection options
if the action is to protect. You might even want to allow them to
protect all sheets, the active sheet only, or select sheets from
another multi-select listbox. (Are we getting complex enough yet?<g>)
 
Hi Garry

I'm really really impressed with your attention to detail , and your
expertise. I have to say it. I've never dealt with anyone quite so
expert.

If I'm honest I'm not technically expert enough to be able to follow
much of what you wrote below , let alone actually implement it. In my
own enthusiastic-amateur way , I just want my answer to the first Yes /
No prompt to be implemented to all sheets in the workbook rather than be
asked for a decision on each one in turn. In the calendar project which
we were discussing this means 12 popups boxes every time. I was hoping
it would be a simple thing.

At any rate Garry , I love your enthusiasm. Keep up the good work.


Best Wishes
 
Colin Hayes pretended :
...I just want my answer to the first Yes / No prompt to be
implemented to all sheets in the workbook rather than be asked for a
decision on each one in turn. In the calendar project which we were
discussing this means 12 popups boxes every time. I was hoping it
would be a simple thing.
Click to expand...

So then, to achieve this more easily, move the iteration into a
front-loader sub and ask the Q there 1x. Revise your protect proc
accordingly. I think you'll be happier with this approach! (If I get
time I'll modify the calendar project to demonstrate this and email a
copy to you)
 
Colin Hayes pretended :

So then, to achieve this more easily, move the iteration into a
front-loader sub and ask the Q there 1x. Revise your protect proc
accordingly. I think you'll be happier with this approach! (If I get
time I'll modify the calendar project to demonstrate this and email a
copy to you)
Click to expand...

Hi Garry

Yes , thank you. That would be very kind.

The protect unprotect macro I have been using , and seek to modify , is
not specific to any workbook. It protects / unprotects any open book you
run it on.

I was seeking a modification whereby a popup would request whether the
user wished a more sophisticated protect with specific permissions , or
a simple one. The Yes or No answer would provide this and act
accordingly on all the sheets in the workbook. It's just a generic
macro. I see entirely what you're saying , and agree with the
modifications you suggest but modifying specific workbooks to match the
macro kind of defeats the generic integrity doesn't it? Or do I have
this wrong?

^_^


Best Wishes
 
Colin Hayes laid this down on his screen :
Hi Garry

Yes , thank you. That would be very kind.

The protect unprotect macro I have been using , and seek to modify , is not
specific to any workbook. It protects / unprotects any open book you run it
on.

I was seeking a modification whereby a popup would request whether the user
wished a more sophisticated protect with specific permissions , or a simple
one. The Yes or No answer would provide this and act accordingly on all the
sheets in the workbook. It's just a generic macro. I see entirely what you're
saying , and agree with the modifications you suggest but modifying specific
workbooks to match the macro kind of defeats the generic integrity doesn't
it? Or do I have this wrong?

^_^


Best Wishes
Click to expand...

Giving the user permission options is the way to go <IMO>. Albeit they
can use the Excel dialog to do this but that means they get to use
their own password (if any). Having our own substitute for the built-in
dialog gives us the best of both, leaving the password issue to our app
only.

Your interest to go this route has inspired me to update my own proc
how I've been wanting to, but never made time to do so. Now I have an
excuse! I think you'll get the idea after you see the sample...
 
Back
Top