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!

 

[Candyman8019]

At least 1 sheet must be visible in a workbook. I'm guessing you don't have one called "Home"? Change that to whatever tab name will always be displayed.

 

[Linki]

At least 1 sheet must be visible in a workbook. I'm guessing you don't have one called "Home"? Change that to whatever tab name will always be displayed.

ohh it was because the workbook was locked - makes sense!
I had locked it, to avoid people accidentally renaming their sheets or hiding them (although the ones they have access to they can also unhide again) - but if they accidentally change the name, somehow - they wouldn't be able to see it anymore hehe

 

[Candyman8019]

Yup, that'll do it.

You can prevent them from changing the tab name by adding the following to each sheet:

private sub worksheet_selectionchange(byval target as range)
    if activesheet.name <> "{the name of the sheet here}" then
       activesheet.name = "{the name of the sheet}"
   end if
end sub

 

[Linki]

Yup, that'll do it.

You can prevent them from changing the tab name by adding the following to each sheet:

private sub worksheet_selectionchange(byval target as range)
    if activesheet.name <> "{the name of the sheet here}" then
       activesheet.name = "{the name of the sheet}"
   end if
end sub

Thats awesome haha I like how it changes it, then just reverts it back to the original sheet name - making people believe they had any sort of say :D Brilliant!