Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide and password group of cells (2003/SP2)

    Here is what I want to do:
    I want to hide a group of cells (Say rows A32 through D45). And Password Protect the these hidden cells so that no one can unhide them until I give them the password. But the rest of the worksheet needs to be "unprotected" so that changes can be made to the rest of the spreadsheet.
    There are formulas in group of cells I wish to hide and I do not want those "filling" out the spreadsheet to be able to see the "results" in the hidden cells before I give them the password to unhide these cells.
    Is there such a way to do this?

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

    Re: Hide and password group of cells (2003/SP2)

    You can hide entire rows (this effectively sets their height to 0) or entire columns (this sets their width to 0), but not a rectangular range of cells. Where would they go? You could set the text color to the same as the background color of the cells, but that won't help much - Excel will still show a "ghost" of the cell values when a larger area including the "hidden" cells is selected. This works even if the sheet is protected and the range itself cannot be selected.
    You might place the cells in a separate worksheet and set its Visible property to xlSheetVeryHidden, so that it can only be made visible using VBA.
    You could then protect the VBA project with a password.
    But even then, a determined user could still break this protection.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide and password group of cells (2003/SP2)

    My bad...there is just information in that range of cells...the entire row can be hidden.
    When I hide those rows, you can tell that rows are hidden cause it skips from 31 to 46. If someone is paying attention, they would be able to see that and then unhide them.
    I will get with someone here to see if they can help me with this VBA stuff...that's way over my head.
    thanks for your help Hans.

    Ruth

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

    Re: Hide and password group of cells (2003/SP2)

    If it's OK to hide entire rows, you could use that. In the Tools | Protection | Protect Sheet dialog, make sure that the Format Rows option is unchecked, so that the user won't be able to unhide the rows or change the row heights.

    But inquisitive users could still create formulas to retrieve the values of the hidden cells...

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hide and password group of cells (2003/SP2)

    In Excel 2003 when you password protect a sheet, you can also elect to uncheck the box allowing users to select locked cells; this prevents them from seeing the underlying formula if you lock the sheet . But to expand on Hans point, sheet protecion is easily broken by simple attacks - techniques have even been posted here in this Forum. I have used the technique Hans prefers of keeping all of my confidential formulus hidden in an "xlVeryHidden" sheet. This macro will provide you with that capability:

    Sub VeryHideSheets()
    ' very hides selected sheets
    Dim wksWS As Worksheet
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    For Each wksWS In ActiveWindow.SelectedSheets
    wksWS.Visible = xlSheetVeryHidden
    Next wksWS
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Set wksWS = Nothing
    End Sub

    And this will undo it if you need:

    Sub UnhideAllSheets()
    ' unhides all worsksheets in workbook
    Dim wksWS As Worksheet
    Application.ScreenUpdating = False
    If Not ThisWorkbook.ProtectStructure Then
    For Each wksWS In ActiveWorkbook.Sheets
    With wksWS
    If Not .ProtectContents Then
    If Not .Visible Then .Visible = True
    End If
    End With
    Next
    End If
    Set wksWS = Nothing
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide and password group of cells (2003/SP2)

    <hr>Excel will still show a "ghost" of the cell values when a larger area including the "hidden" cells is selected. This works even if the sheet is protected and the range itself cannot be selected.
    <hr>
    You can prevent that by using a different technique: use a custom number format of ";;;" (without the quotes).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Hide and password group of cells (2003/SP2)

    OK, good idea.

Posting Permissions

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