Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    password protecting worksheets (2000 SR2)

    Hi, I have a spreadsheet that does pay modelling. The data is in one worksheet and all the modelling is done on another sheet. I regularly issue it to the Unions and I can't allow them to see the data.

    I protected the data sheet by the following excellent macro which I got through this forum:

    Private Sub HideAll()
    Dim wsh As Worksheet
    Dim lngRow As Long
    Dim blnShow As Boolean
    For Each wsh In Me.Worksheets
    blnShow = False
    For lngRow = 2 To Me.Worksheets("Permissions").Range("A65536").End(x lUp).Row
    If Me.Worksheets("Permissions").Range("A" & lngRow) = "Everyone" Then
    If Me.Worksheets("Permissions").Range("B" & lngRow) = wsh.Name Then
    blnShow = True
    Exit For
    End If
    End If
    Next lngRow
    If blnShow Then
    wsh.Visible = xlSheetVisible
    Else
    wsh.Visible = xlSheetVeryHidden

    End If
    Next wsh
    End Sub

    This has worked perfectly but I have noticed a loophole that may accidently allow access.

    If a user opened the spreadsheet and enters the correct password, all the worksheets open (quite correctly). However if that user then sets their macro security to high, then saves the spreadsheet, the following does not run:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    HideAll
    Me.Save
    End Sub

    This means that the workbook is saved with the worksheets unhidden.

    If another person then opens the spreadsheet with high security, the following macro doesn't run:

    Private Sub Workbook_Open()
    HideAll
    frmPassword.Show
    End Sub

    This means that the user can now see all of the sheets (and therefore very sensitive information). Is there anything that I can do about this other than making sure that the folk that know the password, know to check thier security level before saving? (I discovered this when one of our IT bods was trying to fix something else & changed the security setting without telling us).

    Thanks,
    Jim MacLeod
    Shetland Isles

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protecting worksheets (2000 SR2)

    Hi Jim,

    I cannot test your macro of course. But be careful and don't expect too much. Normally sheet protection macros are easily cracked with e.g. Bob Bovey's Excel Utilities 2.0.

    Regards, Teun

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protecting worksheets (2000 SR2)

    There is not much that you can do about that. In addition, if you are depending on Excel security to keep the union from seeing your data, you are treading in VERY dangerous territory. Any union would be sure to have expert Excel employees that can crack ANY excel security in less than one minute (I'm sure there are at least a dozen people around this forum that would be happy to demonstrate that if you sent them a protected copy of your workbook).

    If you really don't want the union to see your data, then I would strongly suggest that you do NOT send them a workbook that contains that data. Make a copy of the workbook and use Copy/Paste Special/Values to remove all formulas from the worksheets that you want the union to see. Then delete the worksheets that contain the data and save that workbook and give it to the union. I would be willing to make a very substantial wager that the unions have already seen any data that was in any workbook that you have given them.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: password protecting worksheets (2000 SR2)

    Thanks for that, I was aware that this protection isn't bullet proof (and can't be), I was just hoping for a way that I could protect it from accidental viewing.

    The problem with the Union is that they need the data to manipulate the model, ie "what if we have 20 hourly rates instead of 15", "how much does 4 years progression cost against 2 years", all that kind of thing.

    Thanks again,
    Jim

    PS I'll keep my eye out for "how do you crack password protected sheets" posts :-)

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: password protecting worksheets (2000 SR2)

    Jim,
    Without knowing what sort of data (and how much of it) we are talking about, it's hard to be specific, but you might consider building a userform to enter and edit the data. That way, the sheets are never unhidden. Of course, if the protection just consists of hiding the sheets, a simple macro will unhide them anyway.
    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
  •