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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,911
    Thanks
    0
    Thanked 89 Times in 85 Posts
    Use:
    Code:
    If Worksheets("Funds").ProtectContents Then
    and reverse the logic, so you unprotect if True.
    Regards,
    Rory
    Microsoft MVP - Excel.

  3. #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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,911
    Thanks
    0
    Thanked 89 Times in 85 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
  •