Forum Discussion
how to create single spreadsheet with user specific access to tabs
Hello Excel savvy people,
We have a 365 A3 license and I need to create an Excel 365 sheet for my boss for workload allotment. She needs one tab as master and then one tab per person in the department. That is easy of course, but she also needs each individual tab to be shared only between she, I and the relevant person directly - also, that person should be allowed to comment (using comments), but not be allowed to make changes in the document. This format would save an approx. 1500-2000 emails a year for us.
Basically:
How do you allow someone access to only one tab in Excel 365 (and not the entire excel file)?
and:
How do you allow that person to comment in Excel 365 (using comments) but NOT to alter the document?
1 Reply
sorenrahr you would need to use VBA to perform that sort of actions. You would check on load/opening for the user name and/or provide a login box and then show only the corresponding tab. As for comments vs. edits if the built in sheet/workbook protection can't be configured accordingly then VBA would be used for that also.
The Problem is that VBA doesn't run in the online version! So IF you are to move forward, you could do all that but the users wouldn't see anything unless they open it in their desktop version (click the little button that says open in app).
Alternatively, you could create a new spreadsheet for each user that pulls data from the master and has permission rights only allowing the corresponding user to open/view. As for comments i would just lock the cells with values in them and give the user a column or 2 open for comments.
