|
Automatic Unprotect / Protect
Hi all, I've got a spreadsheet that a number of people view. I've recorded a macro so that each person can press a button and only their information is displayed. This works fine and dandy. My problem arises where I have protected sheets, which - in recording the macro - I unprotected to run the autofilter, then reprotected once the filtration was complete. I'd like Excel to automatically go through this process rather than prompting the user for the password (the whole point is so they can't change certain columns of data). Here is the macro as it currently stands: Sub PF() ' ' PF Macro ' Macro recorded 06/06/2006 by SamuelT ' ' Sheets("Programme (2 Week)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Programme (High Level)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Capacity").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=5, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Components").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Billing").Select ActiveSheet.Unprotect ActiveWindow.ScrollColumn = 1 Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Extra Fees Calculator").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Programme (2 Week)").Select End Sub Can anyone suggest what I might alter/add/edit to automatically unprotect and reprotect the worksheets? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
Automatic Unprotect / Protect
Hi Samuel,
just add to your lines using ActiveSheet.Unprotect to read as follows: ActiveSheet.Unprotect Password:="xxxx" where xxxx is the password to unlock that sheet. HTH DS "SamuelT" wrote: Hi all, I've got a spreadsheet that a number of people view. I've recorded a macro so that each person can press a button and only their information is displayed. This works fine and dandy. My problem arises where I have protected sheets, which - in recording the macro - I unprotected to run the autofilter, then reprotected once the filtration was complete. I'd like Excel to automatically go through this process rather than prompting the user for the password (the whole point is so they can't change certain columns of data). Here is the macro as it currently stands: Sub PF() ' ' PF Macro ' Macro recorded 06/06/2006 by SamuelT ' ' Sheets("Programme (2 Week)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Programme (High Level)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Capacity").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=5, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Components").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Billing").Select ActiveSheet.Unprotect ActiveWindow.ScrollColumn = 1 Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Extra Fees Calculator").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Programme (2 Week)").Select End Sub Can anyone suggest what I might alter/add/edit to automatically unprotect and reprotect the worksheets? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
Automatic Unprotect / Protect
Thanks DS - much obliged! -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
Automatic Unprotect / Protect
Hi Samuel,
Try this, which should run more quickly too, since there's no selecting/changing sheets: Sub PF() Dim Pwd As String Pwd = "drowssap" With Sheets("Programme (2 Week)") .Unprotect Password:=Pwd .AutoFilter Field:=9, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Programme (High Level)") .Unprotect Password:=Pwd .AutoFilter Field:=9, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Capacity") .Unprotect Password:=Pwd .AutoFilter Field:=5, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Components") .Unprotect Password:=Pwd .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Billing").Select .Unprotect Password:=Pwd .ScrollColumn = 1 .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Extra Fees Calculator").Select .Unprotect Password:=Pwd .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With End Sub Just change "drowssap" to your preferred password. Cheers -- macropod [MVP - Microsoft Word] "SamuelT" wrote in message ... Hi all, I've got a spreadsheet that a number of people view. I've recorded a macro so that each person can press a button and only their information is displayed. This works fine and dandy. My problem arises where I have protected sheets, which - in recording the macro - I unprotected to run the autofilter, then reprotected once the filtration was complete. I'd like Excel to automatically go through this process rather than prompting the user for the password (the whole point is so they can't change certain columns of data). Here is the macro as it currently stands: Sub PF() ' ' PF Macro ' Macro recorded 06/06/2006 by SamuelT ' ' Sheets("Programme (2 Week)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Programme (High Level)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Capacity").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=5, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Components").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Billing").Select ActiveSheet.Unprotect ActiveWindow.ScrollColumn = 1 Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Extra Fees Calculator").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Programme (2 Week)").Select End Sub Can anyone suggest what I might alter/add/edit to automatically unprotect and reprotect the worksheets? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
Automatic Unprotect / Protect
Hi DS,
That leaves the sheets unprotected. You'd need to add the same Password:="xxxx" to the .Protect line too to re-protect the sheet afterwards. Cheers -- macropod [MVP - Microsoft Word] "DS" wrote in message ... Hi Samuel, just add to your lines using ActiveSheet.Unprotect to read as follows: ActiveSheet.Unprotect Password:="xxxx" where xxxx is the password to unlock that sheet. HTH DS "SamuelT" wrote: Hi all, I've got a spreadsheet that a number of people view. I've recorded a macro so that each person can press a button and only their information is displayed. This works fine and dandy. My problem arises where I have protected sheets, which - in recording the macro - I unprotected to run the autofilter, then reprotected once the filtration was complete. I'd like Excel to automatically go through this process rather than prompting the user for the password (the whole point is so they can't change certain columns of data). Here is the macro as it currently stands: Sub PF() ' ' PF Macro ' Macro recorded 06/06/2006 by SamuelT ' ' Sheets("Programme (2 Week)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Programme (High Level)").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=9, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Capacity").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=5, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Components").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Billing").Select ActiveSheet.Unprotect ActiveWindow.ScrollColumn = 1 Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Extra Fees Calculator").Select ActiveSheet.Unprotect Selection.AutoFilter Field:=3, Criteria1:="Peter Farrant" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Programme (2 Week)").Select End Sub Can anyone suggest what I might alter/add/edit to automatically unprotect and reprotect the worksheets? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
Automatic Unprotect / Protect
Forgot to add!
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="xxxx" will reprotect the sheet after the filter's complete (OK, it might seem self-evident, but sooooo many things in VBA aren't!) HTH DS |
Automatic Unprotect / Protect
Hi macropod, Thanks for that. I've just tried to run the macro, but get a run-time error 448. It doesn't seem to like the highlighted line: Sub PF() Dim Pwd As String Pwd = "drowssap" With Sheets("Programme (2 Week)") ..Unprotect Password:=Pwd ..AutoFilter Field:=9, Criteria1:="Peter Farrant" ..Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Programme (High Level)") ..Unprotect Password:=Pwd ..AutoFilter Field:=9, Criteria1:="Peter Farrant" ..Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Capacity") ..Unprotect Password:=Pwd ..AutoFilter Field:=5, Criteria1:="Peter Farrant" ..Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Components") ..Unprotect Password:=Pwd ..AutoFilter Field:=3, Criteria1:="Peter Farrant" ..Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Billing").Select ..Unprotect Password:=Pwd ..ScrollColumn = 1 ..AutoFilter Field:=3, Criteria1:="Peter Farrant" ..Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Extra Fees Calculator").Select ..Unprotect Password:=Pwd ..AutoFilter Field:=3, Criteria1:="Peter Farrant" ..Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With End Sub Any suggestions? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
Automatic Unprotect / Protect
Hi Samuel,
Which line is highlighted? Cheers -- macropod [MVP - Microsoft Word] "SamuelT" wrote in message ... Hi macropod, Thanks for that. I've just tried to run the macro, but get a run-time error 448. It doesn't seem to like the highlighted line: Sub PF() Dim Pwd As String Pwd = "drowssap" With Sheets("Programme (2 Week)") .Unprotect Password:=Pwd .AutoFilter Field:=9, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Programme (High Level)") .Unprotect Password:=Pwd .AutoFilter Field:=9, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Capacity") .Unprotect Password:=Pwd .AutoFilter Field:=5, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Components") .Unprotect Password:=Pwd .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Billing").Select .Unprotect Password:=Pwd .ScrollColumn = 1 .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Extra Fees Calculator").Select .Unprotect Password:=Pwd .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With End Sub Any suggestions? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
Automatic Unprotect / Protect
Hi Samuel
I left two ".Select" statements in there that should be deleted. They're on the lines: With Sheets("Billing").Select and With Sheets("Extra Fees Calculator").Select You might also need to delete the line: ..ScrollColumn = 1 Sorry, for the confusion. Cheers -- macropod [MVP - Microsoft Word] "SamuelT" wrote in message ... Hi macropod, Thanks for that. I've just tried to run the macro, but get a run-time error 448. It doesn't seem to like the highlighted line: Sub PF() Dim Pwd As String Pwd = "drowssap" With Sheets("Programme (2 Week)") .Unprotect Password:=Pwd .AutoFilter Field:=9, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Programme (High Level)") .Unprotect Password:=Pwd .AutoFilter Field:=9, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Capacity") .Unprotect Password:=Pwd .AutoFilter Field:=5, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Components") .Unprotect Password:=Pwd .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Billing").Select .Unprotect Password:=Pwd .ScrollColumn = 1 .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With With Sheets("Extra Fees Calculator").Select .Unprotect Password:=Pwd .AutoFilter Field:=3, Criteria1:="Peter Farrant" .Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password:=Pwd End With End Sub Any suggestions? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=548879 |
| All times are GMT +1. The time now is 12:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com