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?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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.



In article , GS writes
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?

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.

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[s] 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!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

In article , GS writes
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[s] 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!

--
Garry

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)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc