Скачать или смотреть How to Set Read-Only Permissions for Excel Files for Your Entire Organization Using VBA

Discover how to effortlessly manage access for Excel files in your organization using VBA. Allow everyone to read while restricting write access to specific users.
---
This video is based on the question https://stackoverflow.com/q/68347216/ asked by the user 'thoprewa' ( https://stackoverflow.com/u/16431029/ ) and on the answer https://stackoverflow.com/a/71692478/ provided by the user 'Taku' ( https://stackoverflow.com/u/18643488/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA: Set permission to read Excel file to whole organization

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Managing Access to Excel Files with VBA: A Solution for Organizations

Are you facing challenges with setting permissions for Excel files in your organization while adhering to strict IT policies? You’re not alone! Many users encounter similar limitations when trying to manage read and write access on a granular level for individual files. In this guide, we’ll explore how you can achieve read-only access for your organization's members using VBA scripts. Let’s dive into the problem and its solution.

The Problem

Suppose you have a folder containing multiple Excel files that all users in your organization can access. As per your organization’s IT protocols, you cannot set permissions at the folder level but must instead set them file-by-file. On a specific date, you want to lock certain files down, allowing only a select group of individuals to edit them, while others retain the ability to read the content of these files.

While setting permissions manually is straightforward (through File -> Info -> Protect Workbook -> Restrict Access), doing so for numerous files can be tedious. As you are already running a macro for other purposes, you want the ability to automate this process and streamline your workflow.

The Solution: Using VBA

One effective way to implement this is by utilizing VBA to set access permissions for your Excel files. Here's how you can do it step-by-step:

Step 1: Setting Full Control for Specific Users

You can achieve this by using a VBA subroutine like the one below:

[[See Video to Reveal this Text or Code Snippet]]

Workbooks("workbook name"): Replace "workbook name" with the name of your Excel file.

Add("name@ company", msoPermissionFullControl): Modify "name@ company" to include the email addresses of the users you wish to give full access to.

You can loop through a list of user emails if you have several members needing write access.

Step 2: Granting Read-Only Access to the Organization

The trickier part of your request is granting read access to everyone in the organization. Fortunately, rather than using individual email addresses, you can simply use “Everyone” to set the read-only permissions for all members in your organization:

[[See Video to Reveal this Text or Code Snippet]]

In this example, replace "name1@ company" and "name2@ company" with the specific users' email addresses who need writing rights. The line with “Everyone” ensures everyone in your organization can access the files in a read-only format.

Conclusion

This VBA approach allows you to efficiently manage file access without needing to individually adjust settings for all users in your organization manually. By implementing these steps, you can rest assured that the right people have editing permissions while allowing others to access necessary information in your Excel files.

Final Thoughts

If you have further questions or need assistance with setting up your macro, feel free to reach out in the comments below! We hope this guide has provided you with the clarity you need to manage your Excel file permissions with ease.