In this guide, we’ll explain how to set read only recommended in Excel. That means that whenever someone opens it, Excel will suggest they open it as read only.
While setting read only recommended can prevent accidental modifications, users can still choose not to open as read only, so we’ll also cover adding passwords to modify your spreadsheet, as well adding a passwords to open it at all.
Why set read only recommended?
Before we go into the detail, we’ll first give a couple of examples where setting a spreadsheet to be read only is very useful:
- Prevent unintended changes – perhaps you work with other people who will be using your spreadsheet. In some cases, they (or even you) might need to open it but just to use some values. If so, setting read only recommended is good practice – it will prevent someone from accidentally making changes and saving them, assuming they choose to accept and actually do open as read only.
- Avoid locking the file – we’ve all experienced it: you work on a spreadsheet with a few others in your team, a deadline looms and you need to make a quick update to this file, but – oh no – it’s locked for editing by someone else – who could it be? Now you’re using your precious time to find who is in the file, rather than just updating it. You can help to avoid this by turning on read only recommended for the workbook, then only those who need to edit the file will go in to edit it.
Limitations of read only recommended
It’s always good to know potential issues or downsides with what you’re doing so we’ll now explore some of the limitations of the ‘read only recommended’ option in Excel. Some of the key ones are:
- It can be ignored – The prompt will ask the user if they want to open the spreadsheet as read only – clicking ‘Yes’ to open as read only, or ‘No’ to open normally. However, anyone can just click ‘No’ if they like.
- Doesn’t prevent access – Another limitation is it doesn’t prevent anyone from accessing your file. Anyone can still open it as they would, without a password..
- Can be turned off by the user – Another one worth calling out is that anyone can turn off the read only recommended by re-saving the file without it selected. We’ve even included some instructions on how to turn off read only recommended below, for benevolent users only.
Set read only recommended in Excel 365
The simplest way to do this in more recent versions of Excel is via the “File” tab in the ribbon:
- Select “File” from the ribbon then choose “Info”.
- In the information screen, choose “Protect Workbook” > “Always Open Read Only”.
These two steps are shown in the screenshot below.
The next time you save and re-open the workbook it will ask whether you want to open read only.
Set read only recommended
If the above instructions didn’t work (perhaps you have a different version of Excel), then try the following steps to set read only recommended while saving a file.
- Choose File > Save As from the ribbon . Do this even if you don’t want to save the workbook with a different filename. Click ‘Browse’ on the list of locations to get the “Save As” box up, and browse to the location where you want to save the workbook. Alternatively you can press the “Save As” shortcut key: F12 on the keyboard.
- Enter a filename. If you don’t want to change the name of the spreadsheet then just leave this the same as the current name.
- Next to the save button, click (1) “Tools” then (2) “General Options”.
- Another box will open up which has a checkbox for “read only recommended” – make sure that this is selected and click ‘OK’.
- Finally save and close the file.
The next time you open this file, Excel will ask you whether you want to open it as read only.
Set read only recommended using VBA
If you’re developing a macro which saves a spreadsheet, you might also want to set it as read only recommended to prevent users making accidental changes to it (and all the other reasons above).
Tp do this, include the parameter ReadOnlyRecommended := True when calling the Workbook.SaveAs routine. For example, the following code will save the current workbook as a macro enabled file called “MyFile.xlsm” which is read-only recommended.
Sub SaveWorkbook() ThisWorkbook.SaveAs Filename:="MyFile.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, ReadOnlyRecommended:=True End Sub
Set a password to modify an Excel spreadsheet
Making a spreadsheet read only recommended means that anyone can open the spreadsheet, and anyone can still write to it if they choose to. You might want to stop users being able to edit your spreadsheet, for example to prevent accidental changes.
To do this, the process is similar to that for read only recommended: Choose to ‘SaveAs’ the workbook to open the SaveAs dialogue box as shown above:
- Choose File > Save As from the ribbon . Do this even if you don’t want to save the workbook with a different filename. Click ‘Browse’ on the list of locations to get the “Save As” box up, and browse to the location where you want to save the workbook. Alternatively you can press the “Save As” shortcut key: F12 on the keyboard.
- Enter a filename. If you don’t want to change the name of the spreadsheet then just leave this the same as the current name.
- Next to the save button, click (1) “Tools” then (2) “General Options”.
- In the dialogue box that opens, enter the required password in “Password to modify”.
- Press “OK” and re-enter the password at the next prompt. Then press OK again
- Finally, save and close the file. The next time it is opened, your users will need to input the password in order to modify it.
Set a password to modify a spreadsheet using VBA
Just like setting read only recommended using VBA, adding a password to modify a spreadsheet using VBA is fairly simple.
To do this, include the parameter WriteResPassword:="MyPass" when calling the Workbook.SaveAs routine. Replace “MyPass” with whatever you want the password to be set to. For example, the following code will save the current workbook as a macro enabled file called “MyFile2.xlsm” which requires a password to edit.
Sub SaveWorkbook()
ThisWorkbook.SaveAs Filename:="MyFile2.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
WriteResPassword:="MyPass"
End Sub
It’s also possible to combine with read only recommended, and set both at the same time using VBA, as in the following example.
Sub SaveWorkbook2()
ThisWorkbook.SaveAs Filename:="MyFile2.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
ReadOnlyRecommended:=True, WriteResPassword:="MyPass"
End Sub
Set a password to open an Excel spreadsheet
Finally, if your spreadsheet has confidential information, you might need to require a password to open it at all. In more recent versions of Excel, doing so will encrypt the file, such that it’s data can’t be easily read from other programs than Excel. This is set in a similar way to the other cases we’ve discussed above.
Set password to open in Excel 365
In the newer versions of Excel, such as Excel 365, you can do this via the Info section of the File tab:
- Choose ‘Protect Workbook’ > Encrypt with Password’:
- Excel will then open another box for you to enter your password. Heed the warning that if you forget the password you will be unable to open the spreadsheet again and will lose your data. Enter your chosen password and press ‘OK’
- You’ll see a similar box asking to confirm your password. Re-enter and press OK.
- The spreadsheet will now be password protected – anyone trying to open the workbook will need to enter this password.
Set password to open in earlier versions of Excel
If the above instructions did not work (e.g. you couldn’t find the File > Info tab of the ribbon), or you don’t have the ribbon, then follow the steps below instead to add a password from the ‘Save As’ dialogue. These are fairly similar to setting ‘Read Only Recommended’, which we covered above.
- Choose File > Save As from the ribbon . Do this even if you don’t want to save the workbook with a different filename. Click ‘Browse’ on the list of locations to get the “Save As” box up, and browse to the location where you want to save the workbook. Alternatively you can press the “Save As” shortcut key: F12 on the keyboard.
- Enter a filename. If you don’t want to change the name of the spreadsheet then just leave this the same as the current name.
- Next to the save button, click (1) “Tools” then (2) “General Options”.
- In the dialogue box that opens, enter the required password in “Password to open”.
- Press “OK” and re-enter the password at the next prompt. Then press OK again
- Finally, save and close the file. The next time it is opened, your users will need to input the password in order to modify it.
Set a password to open a spreadsheet using VBA
A final way to set a password to open an Excel spreadsheet is using VBA. To do this, we just need to add an extra argument when calling Workbook.SaveAs. In this case, we specify the parameter Password:="MyPass" (replace “MyPass” with whatever you want the password to be)
For example, the following code will save the current workbook as a macro enabled file called “MyFile2.xlsm” which requires a password to open.
Sub SaveWorkbook()
ThisWorkbook.SaveAs Filename:="MyFile2.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
Password:="MyPass"
End Sub
Finally, this can be combined with both read only recommended settings, and a separate password to modify by including those parameters too, as in the following example.
Sub SaveWorkbook2()
ThisWorkbook.SaveAs Filename:="MyFile2.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
ReadOnlyRecommended:=True, WriteResPassword:="SecretPass", Password:="Pass"
End Sub
Remove read only recommended or password from an Excel spreadsheet
Finally, if you want to stop a file from being read only recommended, or remove a password to open/modify, then you’ll just need to follow the above steps in reverse. Note that you will need the password to open the file in order to do this, but you don’t need any password to modify it.
- Choose File > Save As from the ribbon . Do this even if you don’t want to save the workbook with a different filename. Click ‘Browse’ on the list of locations to get the “Save As” box up, and browse to the location where you want to save the workbook. Alternatively you can press the “Save As” shortcut key: F12 on the keyboard.
- Enter a filename. If you don’t want to change the name of the spreadsheet then just leave this the same as the current name.
- Next to the save button, click (1) “Tools” then (2) “General Options”.
- In the dialogue box that opens, uncheck ‘Read only recommened’ and set the passwords to blank (as required).
- Then press ‘OK’, enter a filename and press Save. Note that if you have opened the file as read only, you will need to enter a different filename. If needed, you can then delete/archive the original file and rename yours using Windows Explorer.
Wrap up
In this article we’ve covered how to:
- Set read only recommended in Excel and VBA
- Add a password to modify a spreadsheet in Excel and VBA
- Add a password to open a spreadsheet in Excel and VBA
- Remove read only recommended and passwords from Excel spreadsheets.
If you have any questions, please leave a comment.
References