1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro to protect multiple sheets

Discussion in 'Business Applications' started by JudyB, Sep 24, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. JudyB

    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!
     
  2. Anne Troy

    Anne Troy

    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.
     
  3. XL Guru

    XL Guru

    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
     
  4. Anne Troy

    Anne Troy

    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
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    XL Guru: Yours did not crash my Excel 2000
     
  6. JudyB

    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.
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Do I hear....overkill?

    :D
     
  8. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/167113

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice