Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheets - protect/unprotect

    (Win XP, O2003 SP3 UK)

    Hi,

    In Excel I have an 'admin' menu and a sub menu point 'Protect/unprotect' worksheets. With this single menupoint the user to be able to toggle the worksheets of a protected workbook between protected and unprotected states - provided he has sufficient user rights (being on a postive list of users).

    My code right now looks like this - but it doesn't work as intended.

    For Each c In arrSheets

    'Sheets will be protected
    If Worksheets("Funds").Protect = True Then
    Worksheets(c).Protect Password:=APP_PWDPROTECT

    Else
    'Sheets will be unprotected
    Worksheets(c).Unprotect Password:=APP_PWDPROTECT
    End If

    Next c

    To me it seems that the .Protect/ .unprotect methods don't have a way of testing whether a worksheet is protected or not, eg. by returning a boolean or the like - it can only set .protect or .unprotect.

    Does anyone know of a clever way to do this kind of toggling (without having to promp the user) .


    BR
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    Use:
    Code:
    If Worksheets("Funds").ProtectContents Then
    and reverse the logic, so you unprotect if True.
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Why use the subset .ProtectContents in comparison to "just" .Protect - after all .Protect includes .ProtectContents as well?? (fwik)
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    Protectcontents is not a sub, it's a property that tells you whether the sheet is protected. Since that's what you asked for, that's what I gave you.
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

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