Forum Discussion
Force user to SAVEAS
- Aug 06, 2018
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 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.
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
- Man Fai ChanAug 06, 2018Iron Contributor
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.
- Damien_RosarioAug 06, 2018Silver Contributor
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
- Man Fai ChanAug 08, 2018Iron Contributor
Dear Mr Damien Rosario,
Thank you very much. It will save a lot of time.
Cheers,
Chan M F