Protecting and unprotecting sheets

  • Thread starter Thread starter Microsoft Communities
  • Start date Start date
M

Microsoft Communities

I am trying to protect all sheets in a range.
The sheet names are in date format "01-08-09" through "01-31-09" I have
tried the following and it bombs out on me.
Can someone help me with this.
Thank You


Sub Protect_All_Sheets()
'
' Protect_All_Sheets Macro
' Macro recorded 5/4/2006 by Ed Davis
' Modified 07/30/09
'
' Keyboard Shortcut: Ctrl+p

Application.ScreenUpdating = False
Dim MySelection As Range
Dim MyActCell As Range
'
Set MyActCell = ActiveCell
Set MySelection = Selection

'
Sheets("01-08-09").Select
Range("A1").Select
For I = 1 To 31
ActiveSheet.Protect Password:="7135"
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
Next I
Application.Goto MySelection
MyActCell.Activate
Application.ScreenUpdating = True
End Sub
 
I'm confused about those dates (did you mean January or August?).

But maybe...

Option Explicit
Sub Protect_All_Sheets()

Dim StartDate As Date
Dim EndDate As Date
Dim dCtr As Date

StartDate = DateSerial(2009, 1, 1)
EndDate = DateSerial(2009, 1, 31)

On Error Resume Next
For dCtr = StartDate To EndDate
Worksheets(Format(dCtr, "mm-dd-yy")).Protect Password:="7135"
Next dCtr
On Error GoTo 0
End Sub

The "on error" stuff will ignore any errors caused by missing sheets or by
sheets already protected by a different string.
 
Sorry Dave
I did mean August.
I added a line to your code after where it protects to read
Range ("A1").Select
and it does not go to a1.
any idea why?
Other than that everything works great
thank you for your help.
 
You have to select the sheet before you can select a range.

I threw away that portion of your code so that I could drop the .select's from
the code.

Option Explicit
Sub Protect_All_Sheets()

Dim StartDate As Date
Dim EndDate As Date
Dim dCtr As Date

StartDate = DateSerial(2009, 8, 1)
EndDate = DateSerial(2009, 8, 31)

On Error Resume Next
For dCtr = StartDate To EndDate
with Worksheets(Format(dCtr, "mm-dd-yy"))
.select
.range("A1").select
.Protect Password:="7135"
end with
Next dCtr
Sorry Dave
I did mean August.
I added a line to your code after where it protects to read
Range ("A1").Select
and it does not go to a1.
any idea why?
Other than that everything works great
thank you for your help.
Click to expand...
 
Back
Top