How to prevent overwriting of file ?

[asit.agrawal]

Hi, I have created a template in excel. Now a user would work on
that.. and do some work...BUT while saving, I dont want to all
overwrting the template file i.e the template woudl be named as say,
MyTemplate.xls, so as long user is trying to issue a save and is
working on MyTemplate.xls, I want to force Save As, rather than Save.

Please guide me.
Thanks

 

[Roger Govier]

Hi

When you save your Template for the first time, On the Save as dialogue,
use the dropdown on Save as File type to Select Template (.xlt)

When a user clicks on the file to open it, a copy will open, as Book1, Book2
etc.
When they choose Save, they will be prompted to give a name in place of
Book1 etc.
Your Original Template will still be intact.

 

[asit.agrawal]

Vow.. It works..Great , thanks...
Hey, but is there any way by which I can force a SaveAs when the file
being saved has some specific name, else just save ... ?

 

[Roger Govier]

Hi

The following code entered into the ThisWorkbook module could be used.

You will need to amend to suit your circumstances, and add some checks to
ensure the user doesn't add .xls to the end of the name they give etc., but
it should get you started

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim filename As String, oldname As String
oldname = ThisWorkbook.Name
Application.EnableEvents = False
entername:
filename = ""
filename = InputBox ("Enter a NEW Filename to save this file as")
filename = filename & ".xls"
If oldname = filename Then
MsgBox ("You have chosen the same name " & oldname & vbCr _
& " Choose something different")

GoTo entername
End If
ThisWorkbook.SaveAs filename
Application.EnableEvents = True
End Sub

 

[asit.agrawal]

I Expected something like this only...
but why are u not using SaveAsUI for forcing a SaveAs ?