Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Protect Individual wksheet (Excel 2002)

    Hi

    I am trying to protect individual worksheets, please see code attached.


    When I logon as User Nick B, password nb the code works.
    When I logon as user Alan B, password aab I get an error that say's the password is wrong but I definatley use lower case aab


    Any Ideas please
    If you are a fool at forty, you will always be a fool

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

    Re: Protect Individual wksheet (Excel 2002)

    Have you checked the Caps Lock key?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Individual wksheet (Excel 2002)

    Hi Hans

    Yes it's the first thing I did. If I change the code to another sheet it works It just does not work with Medical Sheet , I've double checked the spaces an speling and case??

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Protect Individual wksheet (Excel 2002)

    It works OK in a test workbook I whipped up. Could you attach a copy of your workbook? You can delete ALL data from ALL worksheets, just leave the relevant sheets, and the code.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Individual wksheet (Excel 2002)

    Hi Hans

    I have located the problem Medical Sheet Had pasword of it's own set, so I changed that to match the password aab and it worked.

    I tried to get the worksheet down down to zip it, but theres is so much in there I struggled to get it down to 100Kb

    Thanks for efforts

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Individual wksheet (Excel 2002)

    Hi Hans

    Subject to recent posts I am now able to open the Medical Sheet, but I need to add other sheets per person. I have made an attempt here as you can see, I have tried to add Acid alk 1
    but it is obviously not the correct procedure, I do not get an error but it just opens Medical Sheet.

    Regards

    Braddy

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim w As Worksheet
    Dim bSaveIt As Boolean

    bSaveIt = False
    For Each w In Worksheets
    If w.Visible Then
    Select Case w.Name
    Case "Medical Sheet"
    w.Protect ("aab")
    w.Visible = False
    bSaveIt = True
    Case "Acid alk 1" <----added
    w.Protect ("aab")
    w.Visible = False
    bSaveIt = True
    Case "RM list"
    w.Protect ("nb")
    w.Visible = False
    bSaveIt = True
    End Select
    End If
    Next w
    If bSaveIt Then
    ActiveWorkbook.CustomDocumentProperties("auth").De lete
    ActiveWorkbook.Save
    End If
    End Sub
    If you are a fool at forty, you will always be a fool

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

    Re: Protect Individual wksheet (Excel 2002)

    That's because in btnOK_Click, you're only displaying Medical Sheet if the username/password combination is Alan B/aab. The code in btnOK_Click provides for only one sheet per user. If you want to display multiple sheets per user, the code will have to be revised considerably.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Individual wksheet (Excel 2002)

    Hi Hans

    Thanks for that, I don't suppose it's feasable for me to try this with my limited knowledge, I am not asking you to do it, but if you could give a little guidance

    I would appreciate it.

    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Protect Individual wksheet (Excel 2002)

    Perhaps the attached workbook (zipped) does what you want.

    It contains a "very hidden" worksheet Permissions with a list of worksheets and users, and a list of users and passwords.
    Worksheets whose user is "Everyone" will always be displayed. All other worksheets are made very hidden when the workbook is closed, and also when the workbook is opened; then the login dialog is displayed and worksheets the user has permission for will be unhidden.

    If the user opens the workbook with macros disabled, or if the user cancels the login dialog, the sheets will remain hidden.

    There is a password on the VBA code. It is "Woody" (case sensitive).

    If you want to view/edit the Permissions sheet, open the workbook, activate the Visual Basic Editor, expand the workbook, provide the password, then set the Visible property for Permisisons sheet to xlSheetVisible in the Properties pane.

    "Hackers" will still be able to crack the protection without much effort.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Individual wksheet (Excel 2002)

    Hi Hans

    Thank you so much, It not top secret stuff I'm looking for, It's just prevent accidental errors by users

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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