Multi user login access to different sheets

gleamng

Board Regular
Joined
Oct 8, 2016
Messages
98
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2011
  7. 2010
  8. 2007
  9. 2003 or older
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.

JohnclakMN
MarkmanagerMNCALLEDGER
SamadminMN0CALLEDGER
BillownerMN0MN1CALTEMPLEDGERUSNMOWNER

<tbody>
</tbody>

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

Code:
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
 
Upvote 0
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
 
Upvote 0
Try this.

Change the Owner page to include the passwords

JohnAAAAclakMN
MarkBBBBmanagerMNCALLEDGER
SamCCCCadminMNCALLEDGER
BillDDDDownerMNCALTEMPLEDGERUSNMOWNER

<tbody>
</tbody>


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


Put this in the form code page:

Code:
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:
Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top