PDA

View Full Version : Override the built in SAVE



JKwan
03-28-2011, 12:53 PM
Wondering if someone may know what is not quite right with the code....
Basicall, if someone hit the SAVE button, I want to override the built in SAVE function with my own, so it gives a versioning of filenames. If I run it as is (traced it and all lines get executed), I don't get my file, nothing, very strange. However, if I attach the code to a button, I get exactly the outcome that I anticipated.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SaveWithVersioning
End Sub

sub SaveWithVersioning
Dim sFileName As String
Dim sCurrentName As String

sCurrentName = Worksheets("SIMOPS").Cells(2, "I")
sFileName = CleanFileName(sCurrentName)
sFileName = ThisWorkbook.Path & "\" & sFileName & "-" & Format(Now, "mmm-dd-yyyy") & ".xls"

If DiskFileExists(sFileName) Then
ThisWorkbook.Save
Else
ThisWorkbook.SaveAs sFileName
End If
end sub

Kenneth Hobs
03-28-2011, 02:14 PM
If a macro errors with Application.EnableEvents=False, run the Application.EnableEvents=True in the Immediate window when testing.

Since I don't have the functions that you posted, I tweaked it a bit.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
SaveWithVersioning
End Sub

Private Sub SaveWithVersioning()
Dim sFileName As String
Dim sCurrentName As String

Application.EnableEvents = False
sCurrentName = Worksheets("SIMOPS").Cells(2, "I").Value2
'sFileName = CleanFileName(sCurrentName)
sFileName = sCurrentName
sFileName = ThisWorkbook.Path & "\" & sFileName & "-" & Format(Now, "mmm-dd-yyyy") & ".xlsm"
If Dir(sFileName) <> "" Then
ThisWorkbook.Save
Else
ThisWorkbook.SaveAs sFileName, xlOpenXMLWorkbookMacroEnabled
End If
Application.EnableEvents = True
End Sub

JKwan
03-28-2011, 02:24 PM
Thank you Kenneth for responding, I had more or less like what you posted (by turning on/off the Application Events), it did not work. It is strange!

mdmackillop
03-28-2011, 02:26 PM
The important difference is
Cancel = True
to abort the Workbook Save

JKwan
03-28-2011, 02:29 PM
Sorry, forgot to mention that, I did have Cancel = True in the BeforeSave module. What gets me is that, if I click the SAVE button, I see all the execution and it hit the "SAVE AS" line, it just don't save. If I put the code into a button, works like a charm!

Kenneth Hobs
03-28-2011, 03:56 PM
It works for me. When the file has not been saved, it will save to your root folder of C:. This is because ThisWorkbook.Path has no path. You might want to use check for that and use curdir or prompt the user for a path.

JKwan
03-29-2011, 06:04 AM
Thanks again for it may be in root of C, however, no go, maybe because I don't have access to root of C. I don't have admin access on my PC :-(.
Another point, I am using Excel 2003, looks like you are using 2007 or 2010, maybe this is another reason? I will continue to hack at it, if not, I will do it my original way, force the user to click my SAVE button, thanks again (hoping others have more ideas for me to try)