@SandsB, in addition to the previous contributions and for the sake of completeness, you can never save a workbook (or any other file) as read-only (RO).
After closing a workbook, its file attribute can be changed within the Windows Explorer or through VBA code, see the example of
@Gokhan Aycan. Obviously this means that this file attribute may no longer be RO when you want to open this workbook yourself. But also keep in mind that what you can do, others can too.
As
@johnnyL said an alternative might be to use a password while saving, that way forcing the read-only status when opening the workbook. In code it looks something like this:
VBA Code:
YourWorkbook.SaveAs Filename:="T:\MyFolder\MyFile.xlsx", CreateBackup:=False, WriteResPassword:="SecretPassword"
In fact, Excel simulates a read-only state using a hidden help file and this method is also not completely watertight. After all, after making changes to the workbook users can save it with a different name, close it and rename it to its original file name within Windows Explorer.
The only safe way is to keep a duplicate in a private folder. Every time you make changes yourself, save the workbook twice; one in your private folder and one with a WriteResPassword in the shared folder. In a network environment, the latter has the advantage that you're able to save the workbook regardless of whether the workbook has been opened by someone else or not. Translated to code, this could look like this:
VBA Code:
With YourWorkbook
' save in current private folder
.Save
' save in shared folder and close immediately
Application.DisplayAlerts = False
.SaveAs Filename:="T:\MyFolder\MyFile.xlsx", CreateBackup:=False, WriteResPassword:="SecretPassword"
.Close SaveChanges:=False
Application.DisplayAlerts = True
End With
Perhaps unnecessary to mention, but if the code is part of the workbook your topic is about, then it should be saved as a macro-enabled workbook, filename ending in .XLSM