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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,523
Members
431,883
Latest member
Hien

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
Top