Restricting specific user sheets

[Linki]

Hi all,

I've been trying all sorts of random solutions I could find online (as I am unable to do VBA myself).

I'm making a sick leave & vacation sheet for my department and my fellow colleagues should only be able to see their own sheet (about 15 sheets, one for each in the team).
There will then be 1 master sheet used for filing paychecks, that compiles the information from all the other sheets - only myself should be able to see this one.

Searching this forum, I am looking for a similar solution as to what was discussed in THIS THREAD (i.e., identifying which person is logged in, and have only that persons respective sheet visible and all others very hidden).

We all use O365 with our work e-mails, hopefully that could be of use in the VBA code as user identifier? I liked the solution that was mentioned with having a "config" sheet, which has a column with usernames (account email?) and columns with which sheet they should be able to access - that would make it easy for someone like me to access and adjust in case of new hires etc.

I want to avoid passworded sheets/files, as it would make it complicated to compile information from multiple passworded locations into a master sheet - as far as I've understood it.

Hope anyone is able to hold my hand and guide my way through this, I appreciate any help I can get, thank you in advance!

 

[ranman256]

if you name all sheets to their user ID, then this will hide them all but the 1 user.

put this code into Workbook_Open event
in spreadsheet, enter VBE (alt-F11)
in left pane (projects) dbl-click the ThisWorkbook object
in right pane (code side), paste the code.

when workbook opens the event will fire.

Private Sub Workbook_Open()
Dim vUser
Dim ws As Worksheet

vUser = getUserID()

For Each ws In Sheets
   ws.Visible = ws.Name = vUser
Next
Set ws = Nothing
End Sub

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

 

[Herakles]

if you name all sheets to their user ID, then this will hide them all but the 1 user.

put this code into Workbook_Open event
in spreadsheet, enter VBE (alt-F11)
in left pane (projects) dbl-click the ThisWorkbook object
in right pane (code side), paste the code.

when workbook opens the event will fire.

Private Sub Workbook_Open()
Dim vUser
Dim ws As Worksheet

vUser = getUserID()

For Each ws In Sheets
   ws.Visible = ws.Name = vUser
Next
Set ws = Nothing
End Sub

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

I would make them Very Hidden and hide the VBA Project as well.

 

[Candyman8019]

Hi Linki,

Give this a try...I have modified the previous example that you had mentioned, to use email address rather than department to display correct sheets. NOTE: The email addresses you specify on the config tab are validated against the logged in user's active directory account.

email tabs-iwantin.xlsm

1drv.ms

 

[Linki]

Hi Linki,

Give this a try...I have modified the previous example that you had mentioned, to use email address rather than department to display correct sheets. NOTE: The email addresses you specify on the config tab are validated against the logged in user's active directory account.

email tabs-iwantin.xlsm

1drv.ms

Hi!
When I enable the macro my excel freezes, every time - I have no idea why. Would it matter that the file is located on our company's onedrive?

 

[Linki]

if you name all sheets to their user ID, then this will hide them all but the 1 user.

put this code into Workbook_Open event
in spreadsheet, enter VBE (alt-F11)
in left pane (projects) dbl-click the ThisWorkbook object
in right pane (code side), paste the code.

when workbook opens the event will fire.

Private Sub Workbook_Open()
Dim vUser
Dim ws As Worksheet

vUser = getUserID()

For Each ws In Sheets
   ws.Visible = ws.Name = vUser
Next
Set ws = Nothing
End Sub

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

Hi Ranman!
Thank you!
I attempted this one too. I had made 3 sheets.
1. My username
2. A colleagues
3. One named Test

It hid 1 + 2 randomly. I was also able to go in and unhide the sheets afterwards without objection which would be a bit too easy access. It doesnt have to be super secure, but a bit more than that would be great - if possible somehow

 

[Candyman8019]

OneDrive shouldn't matter. It may be getting hung up accessing your active directory. hold down the ESC key to stop the script from running. You should be able to step through it from there. Alternatively, I could add a switch to enable/disable the code to make it easier.
You may need to validate what your active directory fields are called. In my organization email is called 'mail' in active directory.

 

[Linki]

OneDrive shouldn't matter. It may be getting hung up accessing your active directory. hold down the ESC key to stop the script from running. You should be able to step through it from there. Alternatively, I could add a switch to enable/disable the code to make it easier.

Is it possible to make it grab it from the O365 account within its own environment? Or maybe thats what its already doing, apologies for being daft!
Just thinking if my colleagues would have to open this to submit their absences, if they would also know how to get "through" the script.

 

[Candyman8019]

I would think O365 would be attached to your active directory...so that should be covered. Your colleagues shouldn't need to do anything with the script. Once you get it running in your organization, it should work for them. One thing to note, when you press ESC to interrupt the code, then go into the config sheet and add your email address there...see if that helps.

 

[Linki]

I would think O365 would be attached to your active directory...so that should be covered. Your colleagues shouldn't need to do anything with the script. Once you get it running in your organization, it should work for them. One thing to note, when you press ESC to interrupt the code, then go into the config sheet and add your email address there...see if that helps.

When I interrupt it and it loads - then all sheets disappear except for Home and the password works well to then show all sheets.
I tried inserting a row with my email and similarly removed the other ones just to test - when I reopened it didnt work again.
When interrupting it this time i tried clicking debug to see and it highlights this: