Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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
0
bvanscoy678
Asked:
bvanscoy678
  • 3
  • 2
  • 2
1 Solution
 
ragnarok89Commented:
Odd,

your macro works fine on my machine, XP SP# and Excel 2003.
0
 
ragnarok89Commented:
that's SP3, by the way
0
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now