VBA: Set permission to read Excel file to whole organization

LinneaBl 1 Reputation point
2021-09-22T11:08:59.733+00:00

So, I've run into a VBA problem. Due to IT policies I can't control read/write access to my Excel files on folder level, but have to do it for each individual file. I have a folder with 15 files, that everyone having access to the folder can access and write in. On a certain date I want to lock these files down, so that only a specific group can write, but the rest can still read.

I'm aware it's realtively easy to do this manually (File->Info->Protect Workbook->Restrict Access), but since I'm already running a macro for other reasons, I'd like for it to do this part too. This is where I run into my problem:

I've figured out how to set the permission for a specific user, using this code

Sub set_access()
Dim UserPerm As Office.UserPermission
Workbooks("workbook name").Activate
   Set UserPerm = ActiveWorkbook.Permission.Add("name@company", msoPermissionFullControl)

I can loop through this for each person I want to have full access, no problem. There's like fifteen of us and I know who we are.

The thing is I also want "everyone in my organization" to retain their reading rights. This has it's own button if I do it manually (in the Permission menu that is accessed as mentioned above), but I can't understand how to trigger that setting via vba. I've tried to record a macro of me doing it manually, to see the code, but nothing is recorded. I've also read just about everthing I could find online like four or five times, and what I need simply doesn't seem to be covered.

Does anyone know if this is poosible? Or if it isn't; can I reach the same results some other way?


tldr: I can set permission by individual, but in addition to this I want to give a general read-only access to my Excel files for anyone in my organization. Can this be done with vba? If so; how?

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,709 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
0 comments No comments
{count} votes