Macro Error - Calling a macro to protect sheets

Putting the finishing touches on a project. I built a macro that will unprotect the sheets, then clear the contents of different ranges, then protect the sheet again.

the unprotect and protect are previous marcro's.

I keep gettin an error when it calls to protect the sheet again. Can't figure out why?
thanks
expert.xls
bvanscoy678Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
royhsiaoConnect With a Mentor Commented:
Try this

Sub clear_sheet()
'
' clear_sheet Macro
' This macro will clear all enteries so it is ready for a new pay period
'

Call DeProtectAll
    Range("A4:C4").Select
   Call DeProtectAll
    Sheets(Array("first day of pay period", "2nd day of pay period", _
        "3rd day of pay period", "4th day of pay period", "5th day of pay period")). _
        Select
    Sheets("first day of pay period").Activate
    Range("A4:C31").ClearContents

Range("D4:D31").ClearContents

Range("F4:F31").ClearContents

Range("B3:C3").ClearContents

Call ProtectAll
End Sub

Public Sub DeProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="678"
Next ws

End Sub

Public Sub ProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.protect Password:="678"
Next ws

End Sub

Open in new window

0
 
ragnarok89Commented:
Odd,

your macro works fine on my machine, XP SP# and Excel 2003.
0
 
ragnarok89Commented:
that's SP3, by the way
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
bvanscoy678Author Commented:
The clear all marco worked?

the protect all and deprotect all work just fine.
It is the Clear all macro that I am having issues with.

thanks
0
 
bvanscoy678Author Commented:
I installed the code and it only clears day1, not the other days.
so I am guessing it is not going into group mode.

I'll keep looking at it.

thanks
0
 
royhsiaoCommented:
oh you could add the following and update the range clear contents    

Sheets("first day of pay period").Activate
    Range("A4:C31").ClearContents
Sheets("2nd day of pay period").Activate
   Range("A4:C31").ClearContents
Sheets("3rd day of pay period").Activate
   Range("A4:C31").ClearContents
Sheets("4th day of pay period").Activate
   Range("A4:C31").ClearContents
Sheets("5th day of pay period").Activate
   Range("A4:C31").ClearContents
0
 
bvanscoy678Author Commented:
yes, this worked perfect.

thanks for the time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.