Excel Macro to protect multiple sheets

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

JudyB

Thread Starter
Joined
May 30, 2003
Messages
98
Does anyone have a macro that can be used "generically" in different workbooks with different number and names of sheets, to protect all the sheets at one time.

I'm getting tired of having to protect or unprotect one sheet at a time in workbooks that have anywhere from 4 to maybe 14 sheets, when I'm making design changes.

So far, the only macro I've been able to come up with is:

Worksheets("January").Activate
ActiveSheet.Protect
*** Repeat for each sheet by name***

Of course this won't work from my "Personal" macro library on other projects because there are a different # of sheets, and they're not named the same.

Thanks!
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
"I'm getting tired of having to protect or unprotect one sheet at a time in workbooks that have anywhere from 4 to maybe 14 sheets, when I'm making design changes."

I heard that!!

Let me look around. I've got a couple of projects where I had the coders put shortcuts:

Ctrl+U (like Unhide)
This unprotects all worksheets, and shows gridlines, column and row headings, etc.

Ctrl+H
This protects all worksheets, and hides gridlines, column and row headings, etc.

You'll see--you'll be able to edit it and everything.
 
Joined
Aug 30, 2003
Messages
2,702
JudyB,

I can't *think* of any reason why the following would make XL crash, however it did *once* for me. However, I think my install's a bit iffy, nevertheless, you have been warned.

Sub ProtAll()
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Protect
Next Sheet
End Sub

Rgds,
Andy
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
Sub HideLayout()
Application.ScreenUpdating = False

With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With

Dim Sh As Worksheet
Dim aSh As Worksheet
Set aSh = ActiveSheet
For Each Sh In ThisWorkbook.Worksheets
Sh.EnableSelection = xlUnlockedCells
Sh.Activate
ActiveWindow.DisplayGridlines = False
With ActiveWindow
.DisplayHeadings = False
.DisplayOutline = False
.DisplayWorkbookTabs = False
.DisplayHorizontalScrollBar = False
End With
If Sh.Index < ThisWorkbook.Worksheets.Count Then Sh.Protect
'This hides certain sheets and you can delete if unwanted
If Sh.Index > 5 Then
Sh.Visible = xlSheetVeryHidden
End If
'End of hide certain sheets
Next Sh
'You'll probably want Sheet1.Activate and "A1").Select
Sheet5.Activate
Sheet5.Range("C4").Select

Application.ScreenUpdating = True
End Sub

________________________________
I did NOT edit this one!

Sub UnhideLayout()
If ActiveWorkbook.Name <> ThisWorkbook.Name Then Exit Sub
Application.ScreenUpdating = False

With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With

Dim Sh As Worksheet
Dim aSh As Worksheet
Set aSh = ActiveSheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Index > 5 Then
Sh.Visible = xlSheetVisible
End If
Sh.Unprotect
Sh.Activate
ActiveWindow.DisplayGridlines = True
With ActiveWindow
.DisplayHeadings = True
.DisplayOutline = True
.DisplayWorkbookTabs = True
.DisplayHorizontalScrollBar = True
End With
Next Sh
aSh.Activate

Application.ScreenUpdating = True
End Sub
 

JudyB

Thread Starter
Joined
May 30, 2003
Messages
98
Thanks to both.
Guru, your's was exactly what I was looking for. Real simple. And it did not crash my PC.
The other was more than I needed.
Thanks.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top