Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Hide & Password Protect Worksheet Based on User (Excel 2002)

    Hi,
    I have a workbook that has multiple worksheets... for this example, I'll say it has 10 worksheets in it. I'm wondering if it's possible to have some of the 10 worksheets automatically hide and password protect themselves based on who the user is opening the file. I'd like to have one file, as opposed to separating out the worksheets 20 ways to Sunday for distribution purposes. Of course it's not the end of the world if someone "cracks" the password (which I doubt they would even try, as they are not hard core Excel / computer users... likewise they are all internal customers so I'm not too worried). I'm thinking a macro could do it, but what if they say "disable" macros when opening the file? Likewise, I wouldn't know how to even attempt writing the macro/VBA for this type of action (at least not yet... I'm signed up for some training... finally!!). I did a search and found that someone had code to hide the worksheets, however it's not exactly what I'm looking for... I'm thinking I'd have a worksheet with each users ID name listed in it and a list of the worksheets each user has access to, and then the macro would do some sort of a vlookup based on the list and unhide and unprotect the listed worksheets for that user?? Any ideas anyone??
    Thanks!!
    Lana

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

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    You could hide all worksheets (except one), so that if the user disables macros, the sheets remain hidden. If the user enables macros, code could unhide specific sheets when the workbook is opened, and hide them again when the workbook is closed/saved. One side-effect you may not like is that the user will be prompted to save changes even if he/she didn't modify anything.
    The attached workbook demonstrates it. There is some general code in Module1 to retrieve the login name of the current user, and code in the ThisWorkbook module that runs when the workbook is opened, closed or saved.
    The Users sheet has been set to xlSheetVeryHidden. You cannot unhide it from the Format | Sheet submenu. You can only do this from the Visual Basic Editor - click on Users in the Project Explorer (the Windows Explorer-like pane on the left hand side) and set the Visible property to -1 - xlSheetVisible. To make it very hidden again, set it to 2 - xlSheetVeryHidden.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    Thanks so much Hans!! This is exactly what I was looking for! I really appreciate your help!
    Lana

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    Hi Hans,

    With minor changes to your 'Workbook_BeforeClose' and 'Workbook_Open' subs, as per the attached, the prompt to save when nothing's been changed by the user can be avoided. The same changes mean the user also won't get prompted if all that's happened is a re-calc via a volatile formula (eg =TODAY()).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    Excellent, thanks!

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    Hi,
    I'm wanting to use part of the code that Hans provided awhile back in this post, however I'm having trouble following it... below is the code I wrote, however I'd like to add Hans code to it. I'm going to have the macro attached to a command button, so when the person who has the file open presses the command button to "approve" it, then the macro will copy their electronic signature and paste it at the bottom of the report. In my macro I have it simply looking at whose name is in cell A19, however I'd like it to look at the username and based on the username, decide which signature to copy & paste. Any help is always much appreciated!
    Thanks so much!!
    Lana

    Sub Approval()

    Sheet3.Select

    If Range("A19") = "scott" Then
    Sheet6.Select
    ActiveSheet.Shapes("Picture 17").Select
    Selection.Copy
    Sheet3.Select
    Range("A26").Select
    ActiveSheet.Paste

    ElseIf Range("A19") = "jerry" Then
    Sheet6.Select
    ActiveSheet.Shapes("Picture 16").Select
    Selection.Copy
    Sheet3.Select
    Range("A26").Select
    ActiveSheet.Paste

    ElseIf Range("A19") = "john" Then
    Sheet6.Select
    ActiveSheet.Shapes("Picture 15").Select
    Selection.Copy
    Sheet3.Select
    Range("A26").Select
    ActiveSheet.Paste

    Else
    Range("A1").Select

    End If

    End Sub

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

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    Application.UserName returns the user name as specified in the General tab of Tools | Options...

    Environ("username") returns the name under which the user logged in to Windows.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    I used the Application.UserName and it worked perfect!
    Thanks Hans!
    Lana

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

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    You have to keep in mind that Application.Username only works if *ALL* users have taken the trouble to fill in their username in the General tab of Tools | Options...

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hide & Password Protect Worksheet Based on User (Excel 2002)

    To add to what Hans has advised; As a user when I log on to the network I do so with a password and the the Environ.UserName. Once logged on I can change my Application.Username to be the same as yours.
    Regards
    Don

Posting Permissions

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