Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding Cells Whilst Sheet is Protected (xl97)

    I think I already know what your answers will be.... but it's worth a try anyway.

    Does anyone know how I can hide and unhide rows on a protected sheet (or set the row heights to zero)?
    I can't unprotect the sheet as the workbook is shared (not ideal I know!), and I can't unshare the workbook as it's constantly in use by more than one user.

    Any help would be much appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding Cells Whilst Sheet is Protected (xl97)

    I'm afraid that you'll get the expected answer. As far as I know, there is no method, even in Visual Basic, to do this. The combination of sharing and protection puts you in a gridlock.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Hiding Cells Whilst Sheet is Protected (xl97)

    The only way I think you can do it is to first get exclusive access to the shared book, unshare it, add 2 buttons the sheet for 'hide rows' and 'show rows', assign VBA code to the buttons (to include password unprotect and re-protect etc) and then save the workbook back in shared mode. You cannot modify VBA code after a workbook is shared, but you can do this before sharing the book. You could pobably test this by opening the shared book, using file-saveas, unshare, do the business with the new book, re-save new file in shared mode, test.
    Shared users can have their own 'view' settings so each should be able to hide/view their own selections.
    If the test file works the way you want you just need to find the right time to do the 'update' when no-one else is using the shared book. When I had to do modifications to VBA code in shared files I usually did it on a weekend when no other users were online.

    zeddy

  4. #4
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Cells Whilst Sheet is Protected (xl97)

    Thanks to both Hans + Zeddy for your quick responses.
    Hans - as I guessed, it can't be done.
    Zeddy - Unfortunately I am unable to use your reply - I can't unshare the workbook, as it is in constant use.

    I was trying to set up a spreadsheet with sheets that had various sections on them, and users would then just select a section in which to enter data by clicking on a macro button (hiding all other sections in the process).
    This worked great initially whilst I was setting up the spreadsheet, by I forgot all about the need to share the sheet (which is vital). Similarly vital is the need to protect the sheet - the users are (how shall I put this...) not always the most clued up on computer technology (and I'm being kind to them there!).

    Thanks for your help anyway.

Posting Permissions

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