VBA to save as read only

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
705
Office Version
  1. 365
Platform
  1. Windows
I open my file, do some things with it then save it. It needs to be saved as read only so when other people open it they don't mess it up. This code looks to me like it should work but the file can still be opened with write access. What have I got wrong here?


ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
ActiveWorkbook.SaveAs Filename:="T:\MyFolder\MyFile.xlsx", CreateBackup:=False
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Tried changing the order?

Also, this works on my test (requires a reference to Microsoft Scripting Runtime):

VBA Code:
Public Sub set_readonly()
 
    Dim fso As FileSystemObject
    Dim oFile As File
 
    Set fso = New FileSystemObject
    Set oFile = fso.GetFile("path to the file")
 
    oFile.Attributes = oFile.Attributes + 1
 
    Set oFile = Nothing
    Set fso = Nothing
 
End Sub

Edit: Above code is after you save it, file was not open. To remove readonly, subtract 1 instead.
MS Documentation for attributes list and their codes.
 
Upvote 0
Select "save as" when you are saving the workbook
Then Select Tools/General options/Tick the Read Only Box and fill in the password boxes. FYI... Tools is just to the left of the save button ;)

Once the file is saved, right click on it, select properties, then select Read only, click apply, click ok
 
Upvote 0
Or

you could place the following code into a ThisWorkbook module...

VBA Code:
Private Sub Workbook_Open()
'
    If Application.UserName <> "YourUserName" Then          ' <--- Replace 'YourUserName' with your actual user name
        ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
    End If
End Sub

That code would check the username when the workbook is opened.

Don't know YourUserName?

Type in:
?Application.UserName into the 'Immediate window' and hit enter.
 
Upvote 0
@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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top