?
Solved

Macro Error - Calling a macro to protect sheets

Posted on 2011-03-10
7
Medium Priority
?
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Accepted Solution

by:
royhsiao earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

719 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