How to restrict access to worksheets per user

[Legacy 374719]

Hi guys, I was wondering whether you could help, I have a workbook with 10 different sheets, that I share with 9 other people, First sheet is what everyone can see, then each has their own work sheet they work with. I need to find a way to restrict access and visibility of other sheets. so that user 1 can see sheet 2 only, user 2 can see sheet 3 only and so on. And lastly I'd like a super user, myself, who can see all sheets. Can anyone advice how to achieve this with VBA?

 

[oldbrewer]

protection lets them see the sheets, but not change them, obviously.

so you hold the master sheet, give each user their own workbook which accesses only 1 sheet in the master, maybe ?

knowledgeable users could change cell refs to access other sheets though........

 

[gallen]

To achieve exactly what you asked you could put this code in the 'ThisWorkbook' module of the Code section:

Private Sub Workbook_Open()
    ShowSheet
End Sub


Sub ShowSheet()
    Dim ws As Worksheet
    Dim wsAllowed As Worksheet
    
    'If SuperUser, show all:
    If Application.UserName = "SuperUserName" Then 'insert Super User's name here
        For Each ws In Worksheets
           ws.Visible = xlSheetVisible
        Next
        Exit Sub
    End If
    
    'If not a super user, only show the associated sheet
    Set wsAllowed = GetAllowedSheet
    wsAllowed.Visible = xlSheetVisible 'make sure the sheet is visible before hiding the others
    
    'Hide all sheets that user isn't permitted to see.
    For Each ws In Worksheets
        If ws.Name <> wsAllowed.Name Then ws.Visible = xlSheetHidden
    Next
    
End Sub


Function GetAllowedSheet() As Worksheet
    'Set the sheet each user is allowed to use here.
    
    'adapt the case statements for correct users and correct sheets to display
    Select Case Application.UserName
        Case "User 1"
           Set GetAllowedSheet = Sheets("Sheet2")
        Case "User 2"
           Set GetAllowedSheet = Sheets("Sheet1")
        Case Else
            'If code gets here then User Name is unhandled.
    End Select
End Function

Naturally it would need modifying to have the correct usernames and to handle what would happen if the username is unknown.


Having said that I'd keep all sheets visible but in the 'WorkSheet_Change' event of each sheet I'd check the username and if they aren't allowed to make any, pop up a message box and undo the change.

 

[Legacy 374719]

Hi gallen, thanks for that I will give it a try, is there a way to add password requirement also to users, just to make sure other users wont get into wrong sheets?

 

[gallen]

Hi gallen, thanks for that I will give it a try, is there a way to add password requirement also to users, just to make sure other users wont get into wrong sheets?

Hi as oldbrewer states you can achieve this somewhat, when you click 'protect sheet' you get the option to password protect it. However you'll get greater control via VBA.

This thread explains how to do it. Let me know if you get stuck on how to implement it. I assume you are ok with wrting and applying simple macros?

 

[Legacy 374719]

thanks gallen I will have a look, I'm fairly new to VBA, I can read the code to make sense where and what needs to be changed, but I don't really know how to use variables (if and when etc) and such, so fairly new, but can tinker enough to get codes working.

Edit:
2 things trying to understand:

1. You say:

Having said that I'd keep all sheets visible but in the 'WorkSheet_Change' event of each sheet I'd check the username and if they aren't allowed to make any, pop up a message box and undo the change.

I can't see the 'WorkSheet_Change' part in the code? or do you mean the 'GetAllowedSheet' part?
2. How will the workbook know which user is accessing, do they type it in somewhere to get access? How will it identify that user 1 is user 1? Just trying to understand how the code works.

 

[gallen]

I can't see the 'WorkSheet_Change' part in the code? or do you mean the 'GetAllowedSheet' part?

When you go in to the code section of the worksheet there are 2 drop down boxes near the top. One will be set to 'General' and the other will be set to 'Declarations'

Change the 'General' drop down to 'Worksheet'. Then the 'Declarations' dropdown will change to 'SelectionChange'. This is the 'events' dropdown and all the possible events for the worksheet are in here. If you change it to 'Change', this code is generated:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

It's within this sub you can execute any code every time a user changes something on the sheet. So for instance, if I wanted to check who is changing it and restrict it id have something like:

Private Sub Worksheet_Change(ByVal Target As Range)

    'if the user isn't the one who is allowed, tell the user and then undo any changes.
    
    If Application.UserName <> "User1" Then
        Application.EnableEvents = False
        MsgBox "You are not authorised to change this sheet", vbCritical, "Change Cancelled"
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

That's just very basic and doesn't handle errors but you can see roughly what I mean.

2. How will the workbook know which user is accessing, do they type it in somewhere to get access? How will it identify that user 1 is user 1? Just trying to understand how the code works.

The line:

Application.Username

gets the username associated with Excel which is stored in the File>Options menu under the 'Gerneral' Tab which should be a sufficient way of deciphering who is accessing the sheet

 

[Legacy 374719]

I see thank you, I need it to be more secure, I can't have even an option for other people to see other sheets. I tried to follow a guide online this one but I couldn't get it to work. maybe you could help ? This does' what I need, give access to specific users only, requesting password and username. I can't use the excel username for various reasons (too convoluted and prone to change).

the above guide gives me following problem: I get error - 'Run-time error '1004': Unable to set the Visible property of the Worksheet class.
Debug highlights this part: Sheets(sSName).Visible = True

and I can't figure out why or how to get around that.

 

[gallen]

Not sure how secure you want it. Anyone who wants to, could easily get in by disabling macros. If they know what they are doing it's straight forward. However try changing

Sheets(sSName).Visible = True

to

Sheets(sSName).Visible = xlSheetVisible

 

[Legacy 374719]

yeah i know, I think there is a way to prevent that also, but first i'd like to get this working. I tried your suggestion, still throwing same error, highlights same line