protecting multiple worksheets but not the entire workbook

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

Thread: protecting multiple worksheets but not the entire workbook

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

    Default

     
    I am in Excel 97 trying to protect multiple worksheets with the same password. Problem is, I don't know how to do it without protecting each sheet individually. I have tried a macro, but the program will not apply the password (still locks the sheet) therefore, it leaves the sheets able to be unlocked by anyone since the pw is not specified in the macro. Any answers?

    Thanks!

    [ This Message was edited by: rdw29 on 2002-07-11 11:27 ]

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select the sheets you wanted protected.

    Then run this macro:


    Sub ProtectMulti()

    Dim wks As Worksheet
    Dim arrSheets() As String
    Dim intI As Integer, intJ As Integer

    ' Read the currently selected sheet names
    ' into an array
    For Each wks In ActiveWindow.SelectedSheets
    intI = intI + 1
    ReDim Preserve arrSheets(intI)
    arrSheets(intI) = wks.Name
    Next wks

    ' This part will un-select the sheets so that we can
    ' protect the ones we want.
    For Each wks In ThisWorkbook.Worksheets
    wks.Select
    Next wks

    ' Now protect the (previously) selected sheets
    For intJ = 1 To intI
    ThisWorkbook.Worksheets(arrSheets(intJ)).Protect "MyPass"
    Next intJ

    End Sub



    It worked for me - it should for you too!

    -Russell

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-07-11 12:27, Russell Hauf wrote:
    Select the sheets you wanted protected.

    Then run this macro:


    Sub ProtectMulti()

    Dim wks As Worksheet
    Dim arrSheets() As String
    Dim intI As Integer, intJ As Integer

    ' Read the currently selected sheet names
    ' into an array
    For Each wks In ActiveWindow.SelectedSheets
    intI = intI + 1
    ReDim Preserve arrSheets(intI)
    arrSheets(intI) = wks.Name
    Next wks

    ' This part will un-select the sheets so that we can
    ' protect the ones we want.
    For Each wks In ThisWorkbook.Worksheets
    wks.Select
    Next wks

    ' Now protect the (previously) selected sheets
    For intJ = 1 To intI
    ThisWorkbook.Worksheets(arrSheets(intJ)).Protect "MyPass"
    Next intJ

    End Sub



    It worked for me - it should for you too!

    -Russell
    HI Russell,

    how did you colour your VBA text on this board ?

    (other than manually)

    it's a nice touch.... please don't tell me you copy pasted each colour command into the HTML

    cheers
    Chris

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
  •  

 

 
DMCA.com