Force Read-Only Save?

JeffK717

New Member
Joined
Sep 29, 2006
Messages
4
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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Sep 29, 2006
Messages
4
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

New Member
Joined
Sep 29, 2006
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,673
Messages
5,549,351
Members
410,910
Latest member
DessertDiva
Top