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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,384
Messages
6,136,274
Members
450,001
Latest member
KWeekley08

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
Back
Top