And more bad news. If you could prevent the deletion of the workbook, you'd
break the way excel works.
When excel saves the file, it saves it as a temporary file with a funny name (8
characters--no extension).
If the save is successful, xl will delete the original (or rename it to its
backup name (like "backup of book1.xlk)) and if that's successful, xl will
rename the funny named file to the original's name.
So I'm not sure you could ever accomplish what you want.
Maybe a work-around. You could create a backup copy of the workbook whenever
anyone saves the workbook. I'd use the date/time as part of the filename so
that I could keep track of the most current. And every so often, I'd go into
that backup folder and clean it up (manually).
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myFileName As String
Dim myFolderName As String
myFolderName = Me.Path & "\backup"
On Error Resume Next
MkDir myFolderName
On Error GoTo 0
myFileName = myFolderName & "\" & Left(Me.Name, Len(Me.Name) - 4) _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"
ThisWorkbook.SaveCopyAs Filename:=myFileName
End Sub
This kind of code goes into the ThisWorkbook module.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Failing this kind of thing, I think the best thing to do is to make and keep
plenty of backups.
