Multiple passwords for different users. Access restricted to certain worksheet

**Declamatory** · 05-27-2016, 06:16 AM

Hi Folks,

I hoping you can help me. I'm looking to allow access to colleagues to a spreadsheet with multiple worksheets. Each colleague would have their own tab that may contain personal information about them and also a summary tab with certain information on all employees that isn't sensitive.

I would like people to have to login to the workbook and depending on the user and password get access to their worksheet and view a summary sheet. I can't use the sheet I need to as it holds personal data on it but I have attached an alternative. It's a holiday chart with the same premise. Each user has their own tab and the information is collated into an overall holiday view so the principle is the same.

I've been doing some research and viewed a number of posts where a user list would be maintained in another tab that would only be viewable to the Admin user. VBA code would unlock/lock the various tabs depending on which user accesses the workbook.

If someone was able to help advise me on what code to use and how to install it I would be grateful.

Thanks

**BadlySpelledBuoy** · 05-27-2016, 06:40 AM

I have a similar set up to this (in a workbook I don't have access to right at this moment) but rather than messing around with passwords, it's all based on network username.
A workbook open event determines the username and displays the relevant sheet for that user. This is based on a list maintained by an Admin user.

Sheets that are not relevant to that user are hidden away using VeryHidden so a user cannot easily just unhide them.

Be wary though, Excel is not massively secure. Anyone with a little skill, the desire to poke around where they shouldn't and a spare 5 minutes could strip password protection from a workbook and access the VBA enabling them to unhide sheets.

To test if this is possible for you, press Alt+F11 to open the VB Editor, press Ctrl+G to open the Immediate Window (this usually appears at the bottom of the VBE). In that window type msgbox environ$("username") and press return. A box should show up and reveal your login ID.

BSB

**TMS** · 05-27-2016, 06:48 AM

To re-iterate what BSB has said, Excel is not a secure environment. It would take only a matter of minutes to make all the worksheets visible, even if the VBA Progect is password protected. Therefore, if you hold IDs and passwords on a worksheet within the workbook, they will be visible, as will all the data. And if you test Network Login IDs and hold them within the code, that is a non trivial maintenance task.

**Declamatory** · 05-27-2016, 06:49 AM

Yes, that does come up with my Login ID

**BadlySpelledBuoy** · 05-27-2016, 07:16 AM

OK, although I highly do not recommend attempting to store very sensitive data in a workbook that you want other people to use you can do it and below is one quick and easy way.

1) Create a new workbook with Sheet1 being the summary sheet you mentioned - For the code below I've called this sheet "Summary"
2) Create another sheet to store the User IDs in column A and associated sheet name in column B
UserID1 - Mary
UserID2 - Mungo
UserID3 - Midge
UserID4 - Etc etc etc

3) Add each of the named sheets
4) Press Alt+F11 to open the VB Editor and double click where it says "ThisWorkbook" somewhere near top left.
5) Paste the code below into the code window
6) Save and close the file
7) Try opening the file as the Admin and as a non Admin user and see if it works.

Private Sub Workbook_Open()

Dim WSCount As Integer
Dim i As Integer

Application.ScreenUpdating = False

WSCount = ActiveWorkbook.Sheets.Count

If Environ$("Username") = "BSB" Then 'CHANGE "BSB" IN THIS LINE FOR THE ADMIN'S USER ID

    For i = 1 To WSCount
        Sheets(i).Visible = True
    Next i

Else

    For i = 1 To WSCount
        If Sheets(i).Name <> "Summary" And Sheets(i).Name <> Application.VLookup(Environ$("UserName"), Sheets("Admin List").Range("A:B"), 2, False) Then
            Sheets(i).Visible = xlVeryHidden
        Else
            Sheets(i).Visible = True
        End If
    Next i

Application.ScreenUpdating = True

End Sub

NOTE: This is untested. All off the top of my head.

NOTE 2: I take no responsibility for any sensitive information that is obtained by persons not entitled.

BSB

**TMS** · 05-27-2016, 07:30 AM

You also need a Before_Close event handler to hide sheets before closing.

Otherwise, you can open the workbook without enabling macros and you'll see someone else's worksheet/data.

**BadlySpelledBuoy** · 05-27-2016, 07:40 AM

Good catch. I missed that because my system at work doesn't ask before enabling macros.

The below also goes in the code window for "ThisWorkbook"

BSB

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim WSCount As Integer
Dim i As Integer

Application.ScreenUpdating = False

WSCount = ActiveWorkbook.Sheets.Count

    For i = 1 To WSCount
        If Sheets(i).Name <> "Summary" Then
            Sheets(i).Visible = xlVeryHidden
        End If
    Next i

Application.ScreenUpdating = True
 
ActiveWorkbook.Save

End Sub

**Declamatory** · 05-27-2016, 08:45 AM

Hey, thanks for your help guys.

However when I run the following code I get the error:

Compile Error:
Block If without End IF

Private Sub Workbook_Open()

Dim WSCount As Integer
Dim i As Integer

Application.ScreenUpdating = False

WSCount = ActiveWorkbook.Sheets.Count

If Environ$("Username") = "Our admin's user ID" Then 'CHANGE "BSB" IN THIS LINE FOR THE ADMIN'S USER ID

    For i = 1 To WSCount
        Sheets(i).Visible = True
    Next i

Else

    For i = 1 To WSCount
        If Sheets(i).Name <> "Summary" And Sheets(i).Name <> Application.VLookup(Environ$("UserName"), Sheets("Admin List").Range("A:B"), 2, False) Then
            Sheets(i).Visible = xlVeryHidden
        Else
            Sheets(i).Visible = True
        End If
    Next i

Application.ScreenUpdating = True

End Sub

**BadlySpelledBuoy** · 05-27-2016, 08:53 AM

Sorry, my bad! Told you it wasn't tested.

Move the end if line to after next I and before application.screenupdating = true.

BSB

**Declamatory** · 05-27-2016, 09:27 AM

Thanks

Now I get a Next without For compile error

**jeffreybrown** · 05-27-2016, 09:04 AM

If you are interested, here's something else worth looking into...

http://www.excelforum.com/excel-new-...passwords.html

**mikerickson** · 05-27-2016, 09:09 AM

Attached is a file that I created to control access to sheets.

The current usernames and (case sensitive) passwords are listed on the first sheet only for demo purposes.
User's can change their password (unknown to administrator) and access their page(s).
Administrators (anyone whose username begins with "admin") can reset passwords to their default and change which sheets a user can access.

**BadlySpelledBuoy** · 05-27-2016, 09:34 AM

Could you post your current code and show which line it errors on?

BSB

**Declamatory** · 05-27-2016, 10:04 AM

Thanks,

It errors on the final Next i just after Sheets(i).Visible = True

Private Sub Workbook_Open()

Dim WSCount As Integer
Dim i As Integer

Application.ScreenUpdating = False

WSCount = ActiveWorkbook.Sheets.Count

If Environ$("Username") = "relevant user id" Then 'CHANGE "BSB" IN THIS LINE FOR THE ADMIN'S USER ID

    For i = 1 To WSCount
        Sheets(i).Visible = True
    Next i

Else

    For i = 1 To WSCount
        If Sheets(i).Name <> "Summary" And Sheets(i).Name <> Application.VLookup(Environ$("UserName"), Sheets("Admin List").Range("A:B"), 2, False) Then
            Sheets(i).Visible = xlVeryHidden
        Else
            Sheets(i).Visible = True
            Next i
    
   End If
    
Application.ScreenUpdating = True

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim WSCount As Integer
Dim i As Integer

Application.ScreenUpdating = False

WSCount = ActiveWorkbook.Sheets.Count

    For i = 1 To WSCount
        If Sheets(i).Name <> "Summary" Then
            Sheets(i).Visible = xlVeryHidden
        End If
    Next i

Application.ScreenUpdating = True

**BadlySpelledBuoy** · 05-28-2016, 02:19 AM

Rather than moving that End If you needed to add another End If. Didn't spot that as I was replying from my phone.
Try the below code:

BSB

Private Sub Workbook_Open()

Dim WSCount As Integer
Dim i As Integer

Application.ScreenUpdating = False

WSCount = ActiveWorkbook.Sheets.Count

If Environ$("Username") = "Our admin's user ID" Then

    For i = 1 To WSCount
        Sheets(i).Visible = True
    Next i

Else

    For i = 1 To WSCount
        If Sheets(i).Name <> "Summary" And Sheets(i).Name <> Application.VLookup(Environ$("UserName"), Sheets("Admin List").Range("A:B"), 2, False) Then
            Sheets(i).Visible = xlVeryHidden
        Else
            Sheets(i).Visible = True
        End If
    Next i
    
End If
    
Application.ScreenUpdating = True

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim WSCount As Integer
Dim i As Integer

Application.ScreenUpdating = False

WSCount = ActiveWorkbook.Sheets.Count

    For i = 1 To WSCount
        If Sheets(i).Name <> "Summary" Then
            Sheets(i).Visible = xlVeryHidden
        End If
    Next i
    
Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub

Multiple passwords for different users. Access restricted to certain worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Re: Multiple passwords for different users. Access restricted to certain worksheet

Thread Information

Users Browsing this Thread

Similar Threads

Excell Multiple Users and passwords

Allow users (with passwords) to view and edit multiple sheets per user.

[SOLVED] Can I set up controls in a worksheet for multiple users to access only their information?

access to worksheet protected by different passwords

Multiple passwords for different users

Restricted activity acces for certain users

Limiting worksheet access through passwords

Bookmarks

Bookmarks

Posting Permissions