Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Make a workbook read-only for everybody but me

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…

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

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.

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?).



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

19 thoughts on “Make a workbook read-only for everybody but me

  1. Matt H says:

    Great, post…loved the simple, yet concise detail of all options related to the making a workbook read-only. Well-written and personable to, easy to read. Thank you!

  2. Hardik says:

    i am Refreshing and Save excel file every 5 minute automatically using macro.
    The following code :

    Sub Button1_Click()
    Call RefreshAllFormulas
    End Sub

    Sub auto_open()
    Call RefreshDataAtInterval

    End Sub

    Sub RefreshAllFormulas()

    Dim i As Integer
    Dim CurCell As Object
    Dim lastCellFx As Object

    MousePointer = fmMousePointerHourGlass
    Total = 0
    For i = 1 To ActiveWorkbook.Sheets.Count
    Subtotal = 0
    On Error Resume Next
    Sheets(i).Activate
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each CurCell In Selection
    If lastCellFx.Formula CurCell.Formula Then
    CurCell.Activate
    mnuRefreshSelection
    Set lastCellFx = CurCell
    End If
    Next CurCell
    Next i
    MousePointer = fmMousePointerDefault
    Sheets(1).Activate
    Call RefreshDataAtInterval
    End Sub

    Sub RefreshDataAtInterval()

    Application.OnTime Now + TimeValue(“00:05:00”), “RefreshAllFormulas”

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs (“C:\Users\WWSUPPORT\Desktop\1032018\TEST ” & Format(Now(), “DD-MMM-YYYY hh mm AMPM”) & “.xlsx”), xlOpenXMLWorkbook

    End Sub
    . now i have open saving excel file but that time macros run again. i
    don’t want to run macros again.

    what code for that . please help me

    • Excel Off The Grid says:

      Hi Hardik,

      Why are you wanting to Save As every 5 minutes? Is it to create a backup of the file? If so you could try:
      ActiveWorkbook.SaveCopyAs
      Instead of:
      ActiveWorkbook.SaveAs

    • Excel Off The Grid says:

      Based on your original code change the SaveAs code to this:

      ActiveWorkbook.SaveCopyAs Filename:=”C:\Users\WWSUPPORT\Desktop\1032018\TEST ” & Format(Now(), “DD-MMM-YYYY hh mm AMPM”) & “.xlsx”

      Note: Sometimes the browser changes the quotation marks, so if you copy and paste you might need to fix these afterwards.

  3. hardik patel says:

    Thank you so much for kindly support me.

    but there was issue to save copy as “.xlsx” file.

    .xlsx file is not supported.

    • Excel Off The Grid says:

      There is not an option to do that using SaveCopyAs.

      Assuming there is no VBA code within the any of the sheets you could try something like the following. It will create a copy of each worksheet listed in a new workbook, then save and close the workbook.

      Dim newWb As Workbook
      Dim currentWb As Workbook

      Set currentWb = ActiveWorkbook
      Set newWb = Workbooks.Add

      currentWb.Sheets(Array(“Sheet1”, “Sheet2″)).Copy Before:=newWb.Sheets(1)
      newWb.SaveAs Filename:=”C:\FilePath\FileName.xlsx”
      newWb.Close

  4. Greg says:

    Has anyone else had issues with errors popping up for others when you have the file open? It works as expected for folks opening in read only when I’m not in the file, but there are issues for others when I have it open.

      • Greg says:

        Would I just need add Notify = True? I’ve added in below but I’m curious if the issue is something else.

        Private Sub Workbook_Open()

        Application.DisplayAlerts = False

        If Application.UserName “My Username” Then

        ThisWorkbook.ChangeFileAccess (xlReadOnly)
        ThisWorkbook.Saved = True
        Notify = True

        Application.DisplayAlerts = True

        End If

        End Sub

  5. Sven says:

    I have a file whose data is only displayed / edited via emeded Userform. Do you have an idea how I set it up that the file is always opened in read-only mode, but the user has an “edit” button, with which he can change the mode? It must be taken into account that no other user edited …

  6. Marcus says:

    Can this macro be used to open the workbook as read only based on the workbook name? When the file is named “Template” then it will open Read Only, but once someone saves under a different name then it will no longer open as Read Only?

    • Excel Off The Grid says:

      You could use an if statement to test for the file path and file name. Here is an example:

      If ThisWorkbook.FullName = "C:\Users\marks\OneDrive\Documents\myTemplate.xlsm" Then
      
      ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
      
      End If

Leave a Reply

Your email address will not be published.