Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Automatic Unprotect / Protect
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
DS DS is offline
external usenet poster
  
Posts: 117
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 329
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 329
Default 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




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
DS DS is offline
external usenet poster
  
Posts: 117
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 329
Default 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 329
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect, unprotect Pyrite Excel Discussion (Misc queries) 0 August 15th 08 03:18 PM
Protect UserInterface VS Protect/Unprotect Desert Piranha[_44_] Excel Programming 2 February 1st 06 03:08 AM
protect and unprotect Andrew[_51_] Excel Programming 1 October 13th 05 07:24 PM
protect and unprotect Andrew[_51_] Excel Programming 2 October 13th 05 06:44 PM
Unprotect and protect.... Wilfrid Excel Programming 1 July 28th 04 03:13 AM


All times are GMT +1. The time now is 02:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement