How to make Excel VBA Read Only

[Sayam]

Hi friends,

I have created a workbook in excel and i don't want anyone to make any changes in it, they should only be able to ready those Excel file and cannot write in that file.In short i need to have my Excel VBA in read only format.Can anyone help me out with this issue.

[Modifier]

The ReadOnly property will not allow you to set the property it's only being used to check whether workbook is ReadOnly or not.For making your workbook Readyonly with all users you need to use SaveAs Method.

[Aarpitt]

Try to use the code for making you Excel workbook ReadOnly

Sub Workbook_Open
   If Environ("Username") = "Administrator" or Environ("Username") = "XYZ"    then
      Workbooks.Open "c:\demo.xls"
   Else
      Workbooks.Open "c:\demo.xls", , True
   End If
End Sub

[Sayam]

I have tried to use the above code for Excel but it's says me do you want to reopen the Excel file which should not happen i should be able to open workbook as ReadOnly at first time only.

[amit shah]

Try to use the below code for making you Excel file in ReadOnly mode.

Private Sub Workbook_Open()
   Application.DisplayAlerts = False
   If Environ("Username") = "Administrator" or Environ("Username") = "XYZ" then
      MsgBox "It's in Edit mode"
      Workbooks.Open "d:\Example.xls"
   Else
      MsgBox "It's in Read Only mode"
      Workbooks.Open "d:\Example.xls", , True
   End If
   Application.DisplayAlerts = True
End Sub

[asina12]

Hi Amit,

How would I modify the code if I want all users to open the workbook in read-only but have a macro that will run automatically everytime the workbook is opened? This macro will need to update some fields, so the workbook should be in write mode, right? But the user has it opened it read-only?

Please share suggest possible solutions, thanks!

[RoiJoy]

Dear All,
Kindly, assist me to look into this code; it throws a compile error "syntax error"

Sub SaveFileReadOnly()
'
' SaveFileReadOnly Macro
'
On Error Resume Next

Call C:\Users\Adam.abc\Desktop\SANPCODES.xlsx

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Adam.abc\Desktop\SANPCODES.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

SetAttr "C:\Users\Adam.abc\Desktop\SANPCODES.xlsx", vbReadOnly
ActiveWorkbook.Close

End Sub

[EINSTEIN_007]

Dear All,
Kindly, assist me to look into this code; it throws a compile error "syntax error"

Sub SaveFileReadOnly()
'
' SaveFileReadOnly Macro
'
On Error Resume Next

Call C:\Users\Adam.abc\Desktop\SANPCODES.xlsx

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Adam.abc\Desktop\SANPCODES.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

SetAttr "C:\Users\Adam.abc\Desktop\SANPCODES.xlsx", vbReadOnly
ActiveWorkbook.Close

End Sub

I dont know what are you trying to ask, but you can test the ReadOnly property of the workbook before trying to save. You can also use the below code to set the ReadOnly property:

SetAttr pathname, attributes

To set ReadOnly=True
SetAttr "C:\Blank.xls", vbReadOnly

To set ReadOnly=False
SetAttr "C:\Blank.xls", vbNormal