Save As

Is there some VBA so if the "Save As" button is pressed, it only works by saving your file as one other file name - ie, if I was working on Book1.xls and pressed the "Save As" button it would force a "Save As" as Book1a.xls so no other option of "Save As" is possible?

Hi Sparx,
I have modified the below code slightly from Nick's post (as per link in comments of code). Copy it into the "Thisworkbook" sheet of the VB Editor & try saving...:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'sourced from Nick Hodge's post at http://www.adras.com/VBA-Excel-File-SaveAs.t469-5.html
Application.EnableEvents = False
Dim NewFileName As String
NewFileName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & "a.xls"
'to stop file saving
Cancel = True
'to check how file was being saved & save as you want it to save.
If SaveAsUI = False Then
MsgBox "You must use ""Save as"" NOT ""Save"".", vbExclamation + vbOKOnly
Else
ActiveWorkbook.SaveAs NewFileName
End If
Application.EnableEvents = True
End Sub

nb: you may need to change the formula on the "NewFileName="... line to get it to work exactly as you want and I haven't included any error checking on hte "saveas" line.

hth, as I'm off to bed now,

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Hi there, Thanks for your assistance - I keep getting a file saved as "False.xls" and have entered the filename where you described me to. I will try to explain a litle easier - I have a file called "Materials Manager.xls" that I use and others use all the time - I dont want others to be able to save the file as anything else using the "Save As" menu - so they can either press the "Save" button to keep updating the information in the "Materials Manager.xls" file or if they do press the "Save As" button, will ONLY let them save the "Materials Manager.xls" to a new file called "Materials Manager - old.xls" and nothing else. It would be great if the "Save As" box didnt even appear but would display "Materials Manager.xls" now saved as "Materials Manager - old.xls"

Hi Sparx,

I'm not sure what would have caused the name to be "false.xls", but I have modified this version based on your feedback/clarification. Also, I have added a unique identifier to the "old" file names so that there isn't the hassle of deciding if you want to "overwrite existing file?".

Try the below,

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'sourced from Nick Hodge's post at http://www.adras.com/VBA-Excel-File-SaveAs.t469-5.html
Application.EnableEvents = False
Dim FilePath As String
Dim NewFileName As String
Dim CurrentFileName As String
Dim TimeCode As String
'to stop file saving
Cancel = True
'to check how file was being saved & save as you want it to save.
Select Case SaveAsUI
Case False
ThisWorkbook.Save
Case True
'to set up variables
FilePath = ThisWorkbook.Path
NewFileName = "Materials Manager - old"
CurrentFileName = ThisWorkbook.Name
'I have added a TimeCode to make each file unique & prevent the hassle of _
responding to "do you want to overwrite existing file?".
TimeCode = " @ " & Left(FormatDateTime(Now, vbShortTime), 2) & "." & Right(FormatDateTime(Now, vbShortTime), 2)
TimeCode = " (" & Day(Date) & "." & Month(Date) & "." & Year(Date) & TimeCode & ")"
'to save & inform
ActiveWorkbook.SaveAs FilePath & "\" & NewFileName & TimeCode
MsgBox """" & CurrentFileName & """" & " now saved, in the same directory, as """ & ThisWorkbook.Name & """."
End Select
ExitSub:
Application.EnableEvents = True
End Sub

BTW, There is probably a tidier way of presenting this code but, like you, I'm still learning too.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Thanks for your help - I have tried and its 99% there - when it saves - its saving correctly - but the version ending in "old" with the time in brackets is not being recognised by my computer - for some reason its losing the .xls making the file a non excel file - also is there a way when it saves using either the save or save-as option - you always remain in the original "Materials Manager.xls" file - and when the new file is created using the "Save-As" option, it makes the output "Materials Manager - Old.xls" as I have written loads of copy and paste vba that writes from the "Materials Manager - Old.xls" file to a new version of the "Materials Manager.xls" file - sorry to take up your time - if I understood VBA in about 12 years time I would do it myself!!

I'm pleased I'm on the right track :-)

Here's a clean version, hopefully I've done everything you want (marked with "'*"):

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'sourced from Nick Hodge's post at http://www.adras.com/VBA-Excel-File-SaveAs.t469-5.html
'to stop it going into an endless "before save" loop by stopping _
Excel from "seeing" the save events in this macro.
Application.EnableEvents = False 'press F9 on this line
'Creating variables for use later
Dim FilePath As String
Dim NewFileName As String
Dim CurrentFileName As String
'to stop file saving (effectively telling Excel that you pressed a cancel button)
Cancel = True
'to check how file was being saved & save as you want it to save.
Select Case SaveAsUI
Case False
ThisWorkbook.Save
Case True
'to identify variables
FilePath = ThisWorkbook.Path
NewFileName = "Materials Manager - old.xls" '*
CurrentFileName = ThisWorkbook.Name
'to save a copy & inform user.
ActiveWorkbook.SaveCopyAs FilePath & "\" & NewFileName '*
MsgBox "A copy of """ & CurrentFileName & """" & " is now saved, in the same directory, as """ & NewFileName & """."
End Select
'to reset Excel's ability to "see" events such as save
Application.EnableEvents = True
End Sub

To overcome the ".xls" issue I have changed the "Newfilename ="... line to explicitly include ".xls". Also, I had wondered if you'd want to stay in the original file & it should now happen.

Two years ago I didn't know VBA existed!
To help bring your learning time down from 12 years, see if you can understand each line of this code by pressing F9 on the line marked in the code (creates a breakpoint), trying to save the file each way possible & pressing F8 to step through the code as it happens line by line (to make it run automatically again, just press F9 on the same line as before). Pressing F5 when you are stepping through it will make it finish that instance of the macro automatically.

Also, just to help you optimise your copy & paste code, have a look at the following links for some tips:

http://www.cpearson.com/excel/optimize.htm
http://excelforum.com/showthread.php...hlight=started (long thread but has a number of questions & solutions)

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Originally Posted by sparx

Thanks for your help - I have tried and its 99% there - when it saves - its saving correctly - but the version ending in "old" with the time in brackets is not being recognised by my computer - for some reason its losing the .xls making the file a non excel file - also is there a way when it saves using either the save or save-as option - you always remain in the original "Materials Manager.xls" file - and when the new file is created using the "Save-As" option, it makes the output "Materials Manager - Old.xls" as I have written loads of copy and paste vba that writes from the "Materials Manager - Old.xls" file to a new version of the "Materials Manager.xls" file - sorry to take up your time - if I understood VBA in about 12 years time I would do it myself!!