Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: protect all sheets macro

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you write a macro to protect all sheets? I have many workbooks that have many sheets. They are veiwed by many people. I have to revise them frequently. The normal Tools, Protection, etc. on every sheet is a pain.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Following should work OK, change the password to suit :-

    Public Sub ProtectAll()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    ws.Protect password:="MyPass", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next ws

    End Sub

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, It works fine.

  4. #4
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have many password protected workbooks/sheets. This is a great help. Thanks.

    Now, can the reverse be done? Unprotecting all sheets at one time?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    (Sort of) the reverse: -

    Public Sub DeProtectAll()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    ws.Unprotect password:="MyPass"
    Next ws

    End Sub

  6. #6
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. This will save me many, many keystrokes!!
    - old, slow, and confused
    ... but at least I'm inconsistent -

    (retired Excel 2003 user, 3.28.2008)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •