This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Make a workbook read-only for everybody but me

Make workbook read only for everybody

Make workbook read only for everybody

I was recently asked this question – “How can I make my workbook read-only?”.  I gave the person an answer.  However, the real question they were asking turned out to be “How can I make my workbook read-only for everybody but me”.  In this post, we will explore some of the options available.

 

Save the file as “read-only recommended”

There is a setting to save the file as read-only recommended.  To use this feature, click File -> Save As.  The Save As window will appear.

Make file Read-Only Save As Window

From the Save As window click Tools -> General Options…


Advertisement:

Make file Read-Only - General Options

From the General Options window select the read-only recommended option.  Click OK.  Then save the file.

When the file is opened, a message is displayed asking the user if they wish to open as read-only.

Make file Read-Only - Open Yes or No

The biggest issue with this option is that the user can click ‘No’.  The file will be opened, but not as read-only.

 

Save the file as “read-only” with a password

Using the same method as described above, a password can be entered.  It is important that the password is only set for modifying the document.  If you set a password to open the document the users will not be able to open the file without a password.

Make file Read-Only General Options Password
Enter a password to make Read-only
Make file Read-Only - General Options Password not Secure
Passwords are not 100% secure

Advertisement:

Passwords entered using this method are not encrypted and can be cracked.  Therefore, anybody who really, really wants to could get access to your document.

This is certainly more stringent than the non-password option.  The user must open the file as read-only unless they have the password.

Make file Read-Only Enter Password

 

Set the file to read-only with a Macro

By using a Macro, it is possible to set the file to read-only when the file is opened.  To enter the Visual Basic Editor press ALT+F11. Select ThisWorkbook for the file you wish to make Read-only.


Advertisement:

Make file Read-Only with VBA

Enter the VBA code below into the code window as shown above:

Private Sub Workbook_Open()

If Application.UserName <> "YourUserName" Then

    ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly

End If

End Sub

Edit the code so that it is your username.  If necessary you can protect your VBA project and save the file (note VBA passwords are also not 100% secure either).

If you do not know your Excel username – in the Visual Basic Editor select View -> Immediate Window (or Ctrl + G).   The Immediate window will open below the code window.  Type the following into the Immediate Window and press enter (see example in the screenshot above).  Your Excel username will appear below.

?Application.UserName

 

What if you need a secure read-only option?

The truth is, you are not able to fully protect your Excel file with just Excel.  If a user has write access to the folder in which the file is saved they could just save down a version of your file, delete the original file, then save a new version with the same name.  Also, as mentioned above, Excel passwords are not secure.  You will have to decide for yourself how bad it would be if somebody were to crack your password or save over your file.

The best option is to restrict access to the folder in which file is saved.  But that is outside the scope of this post (and outside the scope of my ability – that’s why IT departments exist, right?).