Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
GS formulated the question :
'Copy/paste the desired parameters above the commented line. Oops! I forgot to delete the line above. (It's no longer needed here) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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 In article , GS writes 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) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
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. 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) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
In article , GS writes
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. 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) 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying a protect unprotect macro
Colin Hayes laid this down on his screen :
In article , GS writes 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. 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) 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 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... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Qualifying permissions in protect unprotect macro. | Excel Discussion (Misc queries) | |||
Protect and Unprotect all worksheets with macro | Excel Discussion (Misc queries) | |||
Macro to Protect OK, Unprotect now messed up! | Excel Worksheet Functions | |||
400 Error in Unprotect/Protect Macro | Excel Discussion (Misc queries) | |||
protect / unprotect VBA project by macro | Excel Discussion (Misc queries) |