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

Thread: protecting all sheets in a workbook

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Connecticut, USA
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi - I'd like to write a macro to protect all the sheets in a workbook (in Excel 97). I don't want to protect the workbook itself, as I have found that that makes WinZip unable to compress the file by more than about 5% (so whenever I send my 5 linked files over email to a client, it would take 28M instead of a mere 8M). I'm just learning VBA and imagined that I might be able to use a method like Worksheets.Protect, but Protect is not valid for the Worksheets object. My next thought is to write a loop that would Protect each sheet... but I haven't quite got to that point of understanding VBA. Is this indeed the way? Can someone suggest code?

    Thanks,
    Rob

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will protect each sheet with the password ps:

    Code:
    Sub alShts2()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    ws.Protect "ps" 'change ps to your password
    'change 'protect' to 'unprotect' to unprotect each sheet
    Next ws
    End Sub
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-30 15:06 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Connecticut, USA
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That works great - thanks! But (and I should have thought of this before) now if the user knows anything about VBA, s/he can go into the VBE and learn the password. Is there any way to hide the module from public view so as to hide the password?

    Thanks again,
    Rob

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can lock your Project in tools, project prop., protection...

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or you can set up a second workbook to interact with the one in question. Insert this:

    Code:
    ActiveWindow.ActivateNext
    At the beginning and end of the code, having only the workbooks in question open.

    Either way Xl's not the most secure platform...

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    download the file protect sheet from my website..

    http://www.pexcel.com/download.htm

    ni****h desai
    http://www.pexcel.com

  7. #7
    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-04-30 15:01, trf wrote:
    I don't want to protect the workbook itself, as I have found that that makes WinZip unable to compress the file by more than about 5%

    Rob
    I never knew that !

    thanks
    Chris

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
  •