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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Hide & Password Protect Worksheet Based on User (Excel 2002)

    Author
    Topic
    #431634

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1010679

      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.

      • #1010709

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

      • #1010777

        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
        [Fmr MS MVP - Word]

      • #1118912

        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

        • #1118915

          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.

          • #1118918

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

            • #1118919

              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…

            • #1118932

              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.

    Viewing 0 reply threads
    Reply To: Hide & Password Protect Worksheet Based on User (Excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: