Force Read-Only Save?

[JeffK717]

A business client has asked me to modify an existing add-in so that when the user saves the output file, it is read-only (without the user choosing to do so). The XLA file opens an XLT template which it then populates with data, but the user is permitted to save it wherever they want.

How best to proceed with this? If I can't force a Read-only save, can I at least make the workbook password-protected against changes before the user saves it?

 

[Smitty]

Welcome to the Board!

It will require some VBA.

You can use the BeforeSave event and test for the Read-Only status or workbook name. If it doesn't meet your criteria, then set it to read only.

Here's an example of how to set Read/Write access:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> SetAsReadOnly()
    <SPAN style="color:#007F00">'   Test for PC User Name</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strUser <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
        strUser = Environ("USERNAME")
        <SPAN style="color:#007F00">'   MsgBox strUser</SPAN>
        
     <SPAN style="color:#007F00">'  Set Read only File Access for each Office's specific version</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> strUser
        <SPAN style="color:#007F00">'   Full Workbook Access</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "YourUserName", "AnotherUser"
            <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.ReadOnly Then _
                ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, WritePassword:="admin"
        <SPAN style="color:#007F00">'   Limit Access</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <> "YourUserName"
            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ActiveWorkbook.ReadOnly Then _
                ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Or you could have the Read-Only status set as part of the wb creation from the add-in. You can use the GetSaveAsFileName method to allow the user to select where to save after that.

Hope that helps,

Smitty

 

[JeffK717]

Thanks, Smitty. This works fine when added to an open workbook, but it doesn't cover my situation. The existing VBA application is an Add-in that uses a template to create a new Excel file. I tried adding your code to the template's ThisWorkbook_BeforeSave event, but it doesn't get preserved in the new file. Any way to make that happen?

Thanks,
Jeff

 

[JeffK717]

*bump*

 

[btadams]

What about adding smitty's code to the template file?

 

[JeffK717]

What about adding smitty's code to the template file?

That's what I did. But when saving the file at the end of the process, it doesn't seem to execute. The file is saved but is not Read-only.