Multi user login access to different sheets

[gleamng]

Hi
I have a workbook with over 30 sheets and will continue to increase as time goes on, and with most of the sheets having similar names (e.g. MN001 ...). i want to create a multi level login user (admin, manager, clack & owner etc) so that the clack when login will have access to only sheets whose name starts with MN; manager will have access to a sheet called CAL, LEDGER & all MN named sheets; admin will have access to all sheets in the workbook except TEMP, MN1 & USNM; while owner will have access to all sheets in the worksheets. thank you

 

[portews]

Depending on how much security you want, this can either be relatively easy, or a real pain in the neck. If you're just looking to keep the honest (or not so Excel/VBA savvy) people out, it's not too bad. This code takes advantage of the xlSheetVeryHidden property. When this property is set for a sheet, the tab doesn't show up at the bottom. You can still get it to show manually, by going to the VB Editor, clicking on the sheet and changing the visibility to xlSheetVisible. And, there are a few intentional holes in the code to allow you keep from getting locked out if you mess up the user list.

Note, too, that this won't work if people disable the macros. That's the next level of security, making sure macros are enabled before allowing them to see anything. I'm not covering that here.

When you first open the workbook, this code will check the username against a list that you set up with a list of the worksheet types they can see. It will then make those types visible, leaving the rest hidden. When you close the workbook, it will automatically hide all those but the ones everyone can see and save it, setting it up for the next time it's opened.

Begin by creating a new worksheet called OWNER and creating a list of the users, their roles and what types of sheets they can see. Note the differentiation between MN and MN0. I'm not sure I got this right, but some users can see all the MN's and some can not see the MN1. I'm not sure if I got it right or not.

John	clak	MN
Mark	manager	MN	CAL	LEDGER
Sam	admin	MN0	CAL	LEDGER
Bill	owner	MN0	MN1	CAL	TEMP	LEDGER	USNM	OWNER

<tbody>
</tbody>

Then, put this code in the "This Workbook" code page

Private Sub Workbook_Open()
[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/977873-multi-user-login-access-different-sheets.html[/COLOR]
Dim wks As Worksheet
Dim Rng As Range
[COLOR=#00FF00]'Dim x As Integer[/COLOR]
Dim i As Integer
[COLOR=#00FF00]'for testing purposes[/COLOR]
[COLOR=#00FF00]'x = MsgBox("Continue?", vbYesNo)[/COLOR]
[COLOR=#00FF00]'Select Case x[/COLOR]
[COLOR=#00FF00]'Case 7[/COLOR]
[COLOR=#00FF00]'    End[/COLOR]
[COLOR=#00FF00]'End Select[/COLOR]
[COLOR=#00FF00]
[/COLOR]
[COLOR=#00FF00]'search for the user name on the OWNER worksheet, column A[/COLOR]
With Sheets("OWNER").Range("A:A")
    Set Rng = .Find(What:=Application.UserName, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    If Not Rng Is Nothing Then
[COLOR=#00FF00]        'for each worksheet type[/COLOR]
        For i = 3 To Sheets("OWNER").Cells(Rng.Row, Sheets("OWNER").Columns.Count).End(xlToLeft).Column
[COLOR=#00FF00]            'loop thru each worksheet[/COLOR]
            For Each wks In ThisWorkbook.Worksheets
[COLOR=#00FF00]                'if the left part of the worksheet name matches the worksheet type[/COLOR]
                If Left(wks.Name, Len(Sheets("OWNER").Cells(Rng.Row, i))) = Sheets("OWNER").Cells(Rng.Row, i) Then
[COLOR=#00FF00]                        'make the sheet visible[/COLOR]
                        wks.Visible = xlSheetVisible
                End If
            Next
        Next
    Else
[COLOR=#00FF00]        'if the username is not found, throw a userbox[/COLOR]
        MsgBox "Username not found" & vbNewLine & "Contact the Owner"
[COLOR=#00FF00]        'you can put in a ThisWorkbook.Close here, but if you mess up the usernames, you won't get back in.[/COLOR]
    End If
End With
 
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
[COLOR=#00FF00]'loop thru all the worksheets[/COLOR]
For Each sht In Sheets
[COLOR=#00FF00]    'Very Hide all the worksheets except for the MN0[/COLOR]
    If Left(sht.Name, 3) <> "MN0" Then
        sht.Visible = xlSheetVeryHidden
    End If
Next sht
[COLOR=#00FF00]'save and continue with the exit.[/COLOR]
ThisWorkbook.Save
End Sub

 

[gleamng]

thank you for your response, i'm a novice in VBA.
What i want to do is that, the listed users will login through a login user form that will appear anytime the workbook is opened with all the sheets very hidden except INDEX sheet; any user that login will have access to the listed sheets as stated earlier most sheet names are similar (MN001, MN002 ... MN029).
Thank you once more

 

[portews]

Try this.

Change the Owner page to include the passwords

John	AAAA	clak	MN
Mark	BBBB	manager	MN	CAL	LEDGER
Sam	CCCC	admin	MN	CAL	LEDGER
Bill	DDDD	owner	MN	CAL	TEMP	LEDGER	USNM	OWNER

<tbody>
</tbody>

Create the form like this. Don't forget to set the password character on the txtBxPw to *.

Put this in the form code page:

Private Sub UserForm_Activate()
txtBxUrsName = Application.UserName
End Sub


Private Sub btnSelect_Click()
Dim wks As Worksheet
Dim Rng As Range
Dim i As Integer


[COLOR=#00ff00]'search for the user name on the OWNER worksheet, column A[/COLOR]
With Sheets("OWNER").Range("A:A")
    Set Rng = .Find(What:=UserForm1.txtBxUrsName, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    If Not Rng Is Nothing Then
[COLOR=#00ff00]        'check for password[/COLOR]
        If UserForm1.txtBxPw = Sheets("OWNER").Cells(Rng.Row, 2) Then
[COLOR=#00ff00]            'for each worksheet type[/COLOR]
            For i = 4 To Sheets("OWNER").Cells(Rng.Row, Sheets("OWNER").Columns.Count).End(xlToLeft).Column
[COLOR=#00ff00]                'loop thru each worksheet[/COLOR]
                For Each wks In ThisWorkbook.Worksheets
[COLOR=#00ff00]                    'if the left part of the worksheet name matches the worksheet type[/COLOR]
                    If Left(wks.Name, Len(Sheets("OWNER").Cells(Rng.Row, i))) = Sheets("OWNER").Cells(Rng.Row, i) Then
[COLOR=#00ff00]                            'make the sheet visible[/COLOR]
                            wks.Visible = xlSheetVisible
                    End If
                Next
            Next
        End If
    Else
[COLOR=#00ff00]        'if the username is not found, throw a userbox[/COLOR]
        MsgBox "Username not found" & vbNewLine & "Contact the Owner"
[COLOR=#00ff00]        'you can put in a ThisWorkbook.Close here, but if you mess up the usernames, you won't get back in.[/COLOR]
    End If
End With
Unload UserForm1
End Sub


Private Sub btnExit_Click()
ThisWorkbook.Close
End Sub

Put this in the This Workbook code page:

Private Sub Workbook_Open()[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/977873-multi-user-login-access-different-sheets.html[/COLOR]


UserForm1.Show
 
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
[COLOR=#00ff00]'loop thru all the worksheets[/COLOR]
If Not Sheets("INDEX").Visible = xlSheetVisible Then Sheets("INDEX").Visible = xlVisible
For Each sht In Sheets
[COLOR=#00ff00]    'Very Hide all the worksheets except for the INDEX[/COLOR]
    If Left(sht.Name, 3) <> "IND" Then
        sht.Visible = xlSheetVeryHidden
    End If
Next sht
[COLOR=#00ff00]'save and continue with the exit.[/COLOR]
ThisWorkbook.Save


End Sub

 

[gleamng]

ADVERTISEMENT

Thank you so much.
The code worked perfectly but the MN sheets (MN001, MN002, MN003, MN004 ... MN030) did not appear when i login with the listed users. Thanking you in anticipation of your quick response.

 

[gleamng]

Thank you so much.
The code worked perfectly but the MN sheets (MN001, MN002, MN003, MN004 ... MN030) did not appear when i login with the listed users. Meanwhile i have the list of all the MN sheets (MN001, MN002, MN003, MN004 ... MN030) on INDEX sheet column H (H5:H35 and/or more)
Thanking you in anticipation of your quick response.

 

[mole999]

ADVERTISEMENT

you might consider xlsheetveryhidden, so other sheets aren't easily visible

you could code using the actual user logon name with associated levels built into that, therefore login wouldn't be normally needed, but you could trigger a login sheet to elevate privileges

 

[portews]

It's working on my end. Verify that there aren't any extra added spaces or anything else on the Owner page. Add a watch for these two statements and step through the code with the F8 key to see what happens when the code is run.

Left(wks.Name, Len(Sheets("OWNER").Cells(Rng.Row, i)))

and

Sheets("OWNER").Cells(Rng.Row, i)

Both of these are on the line just before it makes the sheet visible and they need to match. Left(wks.Name, Len(Sheets("OWNER").Cells(Rng.Row, i))) for them MN sheets should be "MN" and Sheets("OWNER").Cells(Rng.Row, i) is from the list on the owner page and should match

 

[gleamng]

what i what to happen is this, i dont want to list all the sheets whose names starts with "MN", i want the code to auto display them without having to list them all on the owner sheet, but if it cant be done, except through listing all the MN sheets, then i have them listed in INDEX sheet column H5:H....
thank you so much for your assistance

 

[portews]

When I run the code on my computer, it's is not listing them on the OWNER sheet, it's making them visible. You need to step thru the code on your computer and see what it's doing.