Solved

Macro Error - Calling a macro to protect sheets

Posted on 2011-03-10
7
263 Views
Last Modified: 2012-05-11
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
Comment
Question by:bvanscoy678
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 35095094
Odd,

your macro works fine on my machine, XP SP# and Excel 2003.
0
 
LVL 8

Expert Comment

by:ragnarok89
ID: 35095098
that's SP3, by the way
0
 

Author Comment

by:bvanscoy678
ID: 35095255
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 6

Accepted Solution

by:
royhsiao earned 500 total points
ID: 35097867
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
 

Author Comment

by:bvanscoy678
ID: 35098768
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
 
LVL 6

Expert Comment

by:royhsiao
ID: 35101734
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
 

Author Closing Comment

by:bvanscoy678
ID: 35109536
yes, this worked perfect.

thanks for the time.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Alphabetical Order for Letters 2 21
Openoffice or opensource excel/word/ppt for Mac OSX Mountain Lion 14 44
Clear Filter 8 40
If help 9 48
This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question