Automatic Unprotect / Protect

**SamuelT** · 06-06-2006, 05:19 AM

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

DS · 06-06-2006, 06:10 AM

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
>
>

**SamuelT** · 06-06-2006, 06:13 AM

Thanks DS - much obliged!

macropod · 06-06-2006, 06:25 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

macropod · 06-06-2006, 06:25 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

DS · 06-06-2006, 06:30 AM

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

**SamuelT** · 06-06-2006, 06:34 AM

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

macropod · 06-06-2006, 07:15 AM

Hi Samuel,

Which line is highlighted?

Cheers

--
macropod
[MVP - Microsoft Word]

"SamuelT" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

macropod · 06-06-2006, 07:25 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>