One Workbook, Multiple Users

[Kpbeard]

I would like to put together a workbook with 10 tabs for 10 users.
One is the manager who establishes the data for the other 9.
I would like to send the workbook once to the other nine but only allow them to see their specific tab without being able to see the other 8.
The only one who should see/access everything is the manager.
Everyone else should only be able to see their specific sheet and not be able to view the others.

I've tried password-protecting sheets, but everything is still visible.

I've tried formulas which don't work for this. I guess it would be a macro but I'm not well versed on how to write them or implement them.

I'm using Microsoft 365.

Thank you for any help.

 

[Jeffrey Mahoney]

I would use VBA to Unhide sheets based on their login ID. Sheets can be VERYHIDDEN and you can lock the VBA project so they can't see how to unlock. Only very knowledgeable people would be able to open the sheets. You can password protect the workbook and it will become encrypted so that people are not able to view the underlying HTML data.

VBA can read their login at WB open. You supply a table with the User's login name and which sheets are theirs to modify. The WB opens and makes it visible. You can add Admins so that all sheets are visible.

Jeff

 

[dmt32]

Hi

Protection and security in Excel are not that secure and if you don’t want users to see other people’s data then the only really secure way to achieve this is would be not to send a workbook containing such data to them.

If your users will be sharing your workbook over your employer’s corporate network then one suggestion that may work for you would be to give each user a workbook that contains only their worksheet. They could then read / write their data to and from your master workbook but this approach likely to involve some VBA coding.

Helpful to forum if could share copy of a users worksheet (with dummy data) using MrExcel Addin as others here may have alternative suggestions.

Hope Helpful

Dave

 

[Kpbeard]

I would use VBA to Unhide sheets based on their login ID. Sheets can be VERYHIDDEN and you can lock the VBA project so they can't see how to unlock. Only very knowledgeable people would be able to open the sheets. You can password protect the workbook and it will become encrypted so that people are not able to view the underlying HTML data.

VBA can read their login at WB open. You supply a table with the User's login name and which sheets are theirs to modify. The WB opens and makes it visible. You can add Admins so that all sheets are visible.

Jeff

I like the idea of using VBA, but don't know how to get there. Any ideas?

Keith

 

[Kpbeard]

Hi

Protection and security in Excel are not that secure and if you don’t want users to see other people’s data then the only really secure way to achieve this is would be not to send a workbook containing such data to them.

If your users will be sharing your workbook over your employer’s corporate network then one suggestion that may work for you would be to give each user a workbook that contains only their worksheet. They could then read / write their data to and from your master workbook but this approach likely to involve some VBA coding.

Helpful to forum if could share copy of a users worksheet (with dummy data) using MrExcel Addin as others here may have alternative suggestions.

Hope Helpful

Dave

Hi Dave,

I understand what you're saying about security; things would be easier if it was on a corporate network. However, this being a small office environment, I'm trying to find a way to keep it simple without having the manager send out multiple workbooks.

Keith

 

[dmt32]

However, this being a small office environment, I'm trying to find a way to keep it simple without having the manager send out multiple workbooks.

if you place all the data in one workbook then must accept that there is a real risk someone can access it regardless of any workbook / worksheet protections you may apply.
As for sharing outside the office, it may be worthwhile investigating Working with your data in OneDrive & see if this will help you.

Dave