Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

User level sharing

Copper Contributor

I want to share work book with different users in my office but each one should be able to view his sheet only and an admin to be able to see all the sheets, is that possible? please help.

5 Replies

@Nizar850 

Since the digital environment (Excel version, operating system, storage medium, etc.) is unknown,

I am sending you these links that may be of some help to you.

 

Collaborate on Excel workbooks at the same time with co-authoring

Protection and security in Excel

Restrict access to workbooks with Information Rights Management in Excel

 

If it should or should be in VBA, then you should specify the factors. Example. should the user log into the workbook with his network/windows name? ...or read the name from the list in Excel?

basically something like this if the names are in excel list:

code in ThisWorkbook

 

Private sub workbook_open()

For Each sheets In Sheets()

if application.username="Niko.lino" then
if sheets.name="Sheet2" then Sheet.visible=true:goto next
'... for each sheet of Nikolino
end if

'here as well for serious
'...
scroll.Visible = False

further:
Next
end sub

 

not tested

 

File with a small example is included.

 

Hope I could help you a bit with that.

 

I wish you a happy new year :)

NikolinoDE

 

NikolinoDE

 

Thank you very much Mr. Nikolino, i have downloaded the the sample file, could you please help me on how to make password for each user?
The attached file as well as the VBA code should serve as examples to give you a possible idea to create your desired code.
In order to be able to propose a specific solution, many questions have to be clarified in advance.
As I have already informed you, without more detailed information about the digital environment (Excel version, operating system, storage medium, etc.), it can sometimes be pointless to make such an effort in VBA. Depending on the environment, different options might be available (or not) with or without VBA.

In addition, please keep in mind that imitating a password query in Excel is very insecure and can easily be bypassed.
Thank you, I tried with a VBA controlled file for the same purpose and it works fine with me only but when i share them to others it doesnt work, the message "You have no permission to open this file please contact admin" comes. below is the Exce version and operating system, and the file is stored in a network location.

Thanks,
Nizar
windows 10 Pro
Microsoft 365 version 2211

best response confirmed by Nizar850 (Copper Contributor)
Solution

@Nizar850 

Without knowing how your network is set up, your error message is probably due to missing permissions.

First I would see if the user accepted all macros and contents of the file.

You could also try adding the affected account to the Administrators group and make sure it is given full control to the affected folder.

If everyone can't open the file, check your network's Information Rights Management (IRM).

 

Other approach:

Steps to take to allow VBA macros to run in files that you trust

How you allow VBA macros to run in files that you trust depends on where those file/s are located or the type of file.

 

Please keep in mind, networking and VBA is a painful story in itself. The topic is very extensive and getting a ready-made solution in VBA is rather unlikely. Very often a lot of computing power is required. In my humble opinion, VBA is totally unsuitable for this for several reasons. If there is some code in the network with Excel, I would rather recommend Office Script. But there are also limitations depending on the Excel version.

 

Here is some additional information without VBA that could possibly be used as a workaround.

Protection and security in Excel

Collaborate on Excel workbooks at the same time with co-authoring

 

I can imagine that a lot of information and possible errors can be very irritating :)).

But my knowledge of networks and VBA is also limited, hence this flood of information.

 

However, to include VBA in the network, I recommend adding the administrator because he knows best how the network is set up and what software and version the users use.

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

1 best response

Accepted Solutions
best response confirmed by Nizar850 (Copper Contributor)
Solution

@Nizar850 

Without knowing how your network is set up, your error message is probably due to missing permissions.

First I would see if the user accepted all macros and contents of the file.

You could also try adding the affected account to the Administrators group and make sure it is given full control to the affected folder.

If everyone can't open the file, check your network's Information Rights Management (IRM).

 

Other approach:

Steps to take to allow VBA macros to run in files that you trust

How you allow VBA macros to run in files that you trust depends on where those file/s are located or the type of file.

 

Please keep in mind, networking and VBA is a painful story in itself. The topic is very extensive and getting a ready-made solution in VBA is rather unlikely. Very often a lot of computing power is required. In my humble opinion, VBA is totally unsuitable for this for several reasons. If there is some code in the network with Excel, I would rather recommend Office Script. But there are also limitations depending on the Excel version.

 

Here is some additional information without VBA that could possibly be used as a workaround.

Protection and security in Excel

Collaborate on Excel workbooks at the same time with co-authoring

 

I can imagine that a lot of information and possible errors can be very irritating :)).

But my knowledge of networks and VBA is also limited, hence this flood of information.

 

However, to include VBA in the network, I recommend adding the administrator because he knows best how the network is set up and what software and version the users use.

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

View solution in original post