Force Save As, MS Excel VBA

Question

I am a little new to VBA in MS Office products. I was looking for some code that would force the user to "Save As" .xls file that I use as a template (but this is not the actual type of the template file)

Basically, I have this:

• The user opens .xls, enters some data into some field, and then File → Save As at his location.
• Sometimes the user clicks instead of saving, therefore overwrites the .xls, which I do not want.

I have studied some ideas, but I'm not sure how to implement it best. I think the hint when a user first opens .xls to save in a new location is the best approach, but, thinking ahead, if they already saved the file in a new location and decided to edit a new one, I want them to be able to " Save "at this point, because now it is their own file, not the original.

If someone can point me in the right direction or find flaws in my logic, I would love to hear that.

Thanks Mike

Answer 1

I use as a template (but this is not the actual type of the template file)

The easiest way is to keep the file with Read-Only Recommended true. See Snapshot

These methods, even if the user tries to execute Save , Excel will automatically offer Save As

NTN

Answer 2

I agree with others that read-only templates are the best options, but if you are looking to collapse your own, here is an example to get you started. It is part of ThisWorkbook.

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sNewName As String Cancel = True 'Cancel the save operation sNewName = Application.GetSaveAsFilename(Replace(Me.Name, ".xls", "1.xls")) If sNewName <> Me.FullName And sNewName <> "False" Then Application.EnableEvents = False Me.SaveAs sNewName Application.EnableEvents = True End If End Sub 

Answer 3

I stumbled across this page looking for the same thing, ideally I would like it to save each file as read-only, so that someone should knowingly remove read-only access for editing ... the problem is that if someone makes a change then the document saves as, and then this new document can be edited.

I want to force Excel to save a new document literally every time, unfortunately this code does not work

I press Alt + F11, right-clicking on this book - insert the module - save with marco turned on - then if I make changes, I just save them and don’t open the save window as

what i want maybe? Thank you, guys