VBA filename help

[AHarvey]

Hey,

I have a spreadsheet template that a few rather un-IT people will be editing and they have a habit of saving over the template once they've built the file.

So, I need a piece of VBA code that when the person clicks save, it checks the current file name against the template name and if they match, pops up a msgbox that says something like "Please use SAVE AS and save as another file name!"

My VBA knowledge is poor and tbh, I should learn it myself but I use it that rarely i'm not sure it's worth it.

 

[chaosophy]

Would it not be easier to just password protect/make read only the template? That way they are forced to save as another name.

 

[jsmoke]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ 
        Cancel as Boolean) 
  If Dir(ActiveWorkbook.FullName) <> 0 then 
     Msgbox "File Exists, try another name"
     Cancel = True 
  End if
End Sub

 

[andshrew]

A possibly simpler solution. Save your templates as actual Excel template files ie. .xltx or .xlt - this will then automatically create a new version of the file when they open the file through Explorer or if they go through New->from existing in Excel.

If they go through File->Open in Excel then it would still open the actual template file, so you could put a password on the file to prevent accidental editing.

 

[Svyper1]

I agree with andshrew. It's better practice to use templates.

Checking and saving by programming is for automated saving when it is necessary but there is nothing wrong with creating a macro to give people a reminder.

In fact it would be more complete to help them by using an user defined name via a form triggered by a "control panel" button on the side for example. btn [Save File]

Depends how deep you want the programme to be.