Make Workbook Read Only for everyone except me and 2 others on my team

OSt8Buckeye

New Member
Joined
Jun 17, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Long time Excel user, frequent searcher of answers to problems in the forum, not schooled in VBA in anyway, but have used it somewhat in the past to help solve problems, and first time poster, so go easy on me please.

I have a pricing workbook that I collaboratively work on with my team. The problem is that many other in our organization have access to the folder and therefore the file. Occasionally someone will "inadvertently" change something in the file and we might not notice before we send out a quote. We have tried password protection but it gets too cumbersome with all of the quotes we work on, so we thought that if we could set up the working file to be read/write for the team but read only for everyone else, they have to at least think about saving the file and not just hitting the save button. I have tried multiple versions of code with some success, but nothing that will allow me to make this work. I am purposefully not including my code ( I am not very good at VBA by any stretch ) as I don't want to limit thoughts on how best accomplish this reasonably simple task.

Now my pricing workbook is 30 plus tabs, with hidden tabs, very hidden tabs, circular references, and formulas all over the place referencing other formulas and data points from elsewhere in the workbook. I am currently using Office 2016, but am to be upgraded to Office 365 in the near future - not sure if that affects the solution or not.

If you want my attempted code, let me know, happy to post.

Thanks for the help
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Sequoyah

Board Regular
Joined
Mar 26, 2017
Messages
65
Hi OSt8Buckeye,
try this code in ThisWorkbook Module
VBA Code:
Private Sub Workbook_Open()

    If Application.UserName <> "Tom" And Application.UserName <> "John Doe" And Application.UserName <> "Harry" Then
    
        ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
    
    End If

End Sub
 

Forum statistics

Threads
1,144,424
Messages
5,724,260
Members
422,542
Latest member
jedidia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top