Save As

[sparx]

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?

[broro183]

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...

[sparx]

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"

[broro183]

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...

[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!!

[broro183]

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...

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!!

[sparx]

Broro183, Thank you - its worked an absolute treat - I will keep take your advice regards stepping through VBA as you have written for me. Again, thank you.

[sparx]

Broro183, Please can I ask for your help once more - I already have in my file "Materials Manager.xls" in the "ThisWorkbook" sheet, a private sub Workbook_BeforeSave that basically does something on saving that has to stay - now I have your code, Excel throws a wobbly when I add your code and try to save the whole file - saying "Compile error: Ambiguous name detected: Worksook_BeforeSave. Can your code be written into a module and in the sheet "ThisWorkbook", I can add a "Private Sub Workbook_Open() - Run your vba or so?

[sparx]

Broro183, I have found out what Option Explicit means and have put this to the top of my "ThisWorkbook" page - I had it half way down. Did the file keep looping when you tried your VBA, if I run Materials Manager and save then fine - the file saves - if I press save as, then your vba runs and a new file is saved - if I click close, then I am asked to save so I do - then the page wont close - it keeps saying "Do you want to save changes you made to Materials Manager.xls?" when I have not made any changes - any thoughts?

[broro183]

Hi Sparx,

re "ambiguous name detected":
The name of each macro in a project must be unique when the VBE compiles the code. In this case you have 2 options that I know of:
* Add the whole of my code into the macro that already exists & decide if it is best before or after the code you already have (I suspect it would be better after - but am only guessing).
*To make things tidy you could rename this macro NamingSavedFile, save it to a module & put the line "Call NamingSavedFile" in an appropriate place of the "Worksook_BeforeSave".

Broro183, Please can I ask for your help once more - I already have in my file "Materials Manager.xls" in the "ThisWorkbook" sheet, a private sub Workbook_BeforeSave that basically does something on saving that has to stay - now I have your code, Excel throws a wobbly when I add your code and try to save the whole file - saying "Compile error: Ambiguous name detected: Worksook_BeforeSave. Can your code be written into a module and in the sheet "ThisWorkbook", I can add a "Private Sub Workbook_Open() - Run your vba or so?

re the looping:
To be honest I don't know how to stop this, can anyone else help?

Try adding the below code to the "thisworkbook" module, it may help. What makes it work is the unmatched "application.enableevents" lines. However, if a user disables macros when opening, the events will remain disabled (not good).
If this works well enough for you, good, otherwise have a Google/search groups for phrases like "forcing user to enable macros".

Private Sub Workbook_Open()
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim response As Boolean
Application.EnableEvents = False
response = MsgBox("Do you want to save changes?", vbYesNo)
If response Then
ActiveWorkbook.Save
End If
End Sub

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