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?


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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"


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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

sparx Wrote:
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
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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?


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=537559

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

sparx Wrote:
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...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=537559