none
VBA Code to save my excel 2013 file as Read Only (or Non Editable) ... RRS feed

  • Question

  • Hi,

    ActiveWorkbook.SaveAs SFilePath & udds & ".xlsm", xlWorkbookNormal
    SetAttr SFilePath & udds & ".xlsm", vbReadOnly
    Application.DisplayAlerts = True

    I have used the above code to make my file readonly ...however it still allows to make changes to the file and sometimes the file does not get open and throws an error " Excel cannot open the file ".xlsm" because the file format or file extension is not valid......."

    Could you please help me this


    Regards, Hitesh

    Tuesday, February 3, 2015 5:17 PM

Answers

  • Hi,

    ActiveWorkbook.SaveAs SFilePath & udds & ".xlsm", xlWorkbookNormal
    SetAttr SFilePath & udds & ".xlsm", vbReadOnly
    Application.DisplayAlerts = True

    I have used the above code to make my file readonly ...however it still allows to make changes to the file and sometimes the file does not get open and throws an error " Excel cannot open the file ".xlsm" because the file format or file extension is not valid......."

    Could you please help me this


    Regards, Hitesh

    Saving the file as read-only does not mean you cannot make changes, it merely means it is recommended that you open this file as read-only. Also using 'xlWorkbookNormal' is incorrect since this is basically the macro-free xml format. Use the 'xlOpenXMLWorkbookMacroEnabled' instead. Now as far as not allowing the user to make changes you will need to protect each worksheet, as well as protect the workbook. So something like

    Sub ProtectWorkbook()
        ThisWorkbook.Protect Structure:=True, Password:="test123"
        Dim sh As Worksheet
        For Each sh In ThisWorkbook.Worksheets
            sh.Protect Password:="Test1234"
        Next sh
    End Sub

    Tuesday, February 3, 2015 6:09 PM

All replies

  • Hi,

    ActiveWorkbook.SaveAs SFilePath & udds & ".xlsm", xlWorkbookNormal
    SetAttr SFilePath & udds & ".xlsm", vbReadOnly
    Application.DisplayAlerts = True

    I have used the above code to make my file readonly ...however it still allows to make changes to the file and sometimes the file does not get open and throws an error " Excel cannot open the file ".xlsm" because the file format or file extension is not valid......."

    Could you please help me this


    Regards, Hitesh

    Saving the file as read-only does not mean you cannot make changes, it merely means it is recommended that you open this file as read-only. Also using 'xlWorkbookNormal' is incorrect since this is basically the macro-free xml format. Use the 'xlOpenXMLWorkbookMacroEnabled' instead. Now as far as not allowing the user to make changes you will need to protect each worksheet, as well as protect the workbook. So something like

    Sub ProtectWorkbook()
        ThisWorkbook.Protect Structure:=True, Password:="test123"
        Dim sh As Worksheet
        For Each sh In ThisWorkbook.Worksheets
            sh.Protect Password:="Test1234"
        Next sh
    End Sub

    Tuesday, February 3, 2015 6:09 PM
  • Thanks a lot.....this helps

    Regards, Hitesh

    Wednesday, February 4, 2015 8:30 PM