SOLVED

Force user to SAVEAS

Iron Contributor

I have a file to put in a shared folder. It served as a template for others. Other users may open and write the file but I don't want them to save the "template" file. Is there a way to prevent them save changes in the "template" file? Thanks a lot. 

12 Replies

Hi @Man Fai Chan

 

If the shared folder is on SharePoint or OneDrive, you can choose to give READ only access to the template file.

 

This will stop people from editing the original template but they can still get access to a copy (template) of the file to use as required.

 

If you need to set the permissions to do that. Find the file on OneDrive or SharePoint online, you can click Share and configure the settings, or view the details of the file and change the file permissions.

 

If your file is stored locally on a file drive, then you can right click onto the file > Properties > Security tab to change file permissions.

 

Hope that helps.

 

Cheers

Damien

 

 

Mr. Chan

The recommendation of Mr. Rosario is what I have been doing to prevent my templates from being written over.

However, if the user (with bad intention) knows the trick to change it in the file properties - no one can stop that!

Maybe what you are asking is a vba to do that protection. I wonder, if there is...

MVP's.. anyone?

actually, I had been searching the net for forced SAVEAS - but no luck.. still, I think there is a code for that.
Thanks for your thoughts, Lorenzo.

Man Fai Chan would only need to do the VBA route if people are purposefully change the template.

I suspect it's more about users accidently editing it versus purposefully changing it which isn't hard to do.

If it is on purpose then people are being very naughty!

Cheers
Damien
Mr. Rosario,
Thanks for the info. May I request for that VBA - force a SAVEAS.
many many thanks

It might be a forum violation - so I am making my own conversation....

topic = SAVEAS

thank you

Thanks Kim's and Rosario's reply. 

 

The file is saved in a networked drive, so that I cannot use the ONE DRIVE/SHAREPOINT. The file is a form for others to complete and then submit to the person-in-charge. I actually made macro to check if they completed the form or not. 

 

Thanks for you two to inspire me the following code:

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
Else
If ThisWorkbook.Name = "Template.xlsb" Then
x = InputBox("This is the template file, you need password to save")
If x <> "CORRECTPASSWORD" Then
Cancel = True
Else
Cancel = False
End If
Else
Cancel = fasle
End If
End If

End Sub

 The use of "CORRECTPASSWORD" is actually a way for me (as the writer) to save file. However, I found that the user can still SAVEAS the template file and overwrite it in the save-window.

 

 

Mr. Chan

Thank you for the input..

best response confirmed by Man Fai Chan (Iron Contributor)
Solution

Hi @Man Fai Chan

 

Good to hear you have some ideas to go forward with.

 

Out of curiousity, is your network file saved as an Excel Macro-enabled template (*.xltm)?

 

Cheers

Damien

Thanks for your clue......

 

I actually did not know .xltm before. It is an easier way to perform the so called "Force Save-as" function. 

Thanks a lot. 

Hi @Man Fai Chan

 

Yes, if you use this format, when users double click onto the file it will always load a blank copy of the workbook which they can save and do with as they will, leaving the template intact (a lot easier than macro code).

 

They could still edit the file by opening it from Excel and editing the original template, but they would truly have to go out of their way to want to do that whereas this option is simple, open the file, fill in the details, and send to the relevant person.

 

Glad you have found some options to suit and happy to help.

 

Wishing you all the best friend!

 

Cheers

Damien

Dear Mr Damien Rosario,

 

Thank you very much. It will save a lot of time. 

 

Cheers,

Chan M F

 

1 best response

Accepted Solutions
best response confirmed by Man Fai Chan (Iron Contributor)
Solution

Hi @Man Fai Chan

 

Good to hear you have some ideas to go forward with.

 

Out of curiousity, is your network file saved as an Excel Macro-enabled template (*.xltm)?

 

Cheers

Damien

View solution in original post