Unexplained WorkbookBeforeSave SaveAs Error Causes Excel to Crash

[JT Klipfer]

I have created a fairly extensive (for me anyway) code set that will
ultimately become an Excel 2003 addin for my team. We have recently begun an
effort to implement strict ISO requirements for document version control; I'm
trying to automate this process with this future addin. So, what I've done
is created a series of custom fields to contain the required components of my
filenames and am using the code below to automatically update the version &
subsequently save as a new file with the version numbers in the file name.

The only problem is that something about the ActiveWorkbook.SaveAs makes
Excel crash every time it SavesAs a new (because of the changed ver. numbers
appended to the filename) file. Once I restart Excel, everything is fine and
even the new file works just fine.

Strangely enough though, if I manually manipulate the version fields so that
when I save it, it would create a filename of a file that already exists, it
asks me if I wanted to replace, and then everything works fine. Obviously, I
want to create a new file each and every save, but I can't seem to figure out
what's making it crash. The same code also works in 2007 on my PC at home.

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, _
Cancel As Boolean)
Determine
Dim UpdAnswer, Ext As Variant
UpdAnswer = MsgBox("Is this revision a major update?", vbQuestion +
vbYesNoCancel, _
"Version Verification")
Ext = Right(ActiveWorkbook.Name, 4)
If UpdAnswer = vbCancel Then
Exit Sub
ElseIf UpdAnswer = vbYes Then
ActiveWorkbook.CustomDocumentProperties("MajNo") = _
ActiveWorkbook.CustomDocumentProperties("MajNo") + 1
ActiveWorkbook.CustomDocumentProperties("MinNo") = 0
ElseIf UpdAnswer = vbNo Then
ActiveWorkbook.CustomDocumentProperties("MinNo") = _
ActiveWorkbook.CustomDocumentProperties("MinNo") + 1
End If
Application.EnableEvents = False
ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" &
ActiveWorkbook.CustomDocumentProperties("DocName") & _
"_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _
ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext)
Application.EnableEvents = True
Cancel = True
End Sub

 

[Dave Peterson]

First, I wouldn't rely on the workbook you're saving being the activeworkbook.

Since you're tying into that App_WorkbookBeforeSave and passing the workbook
(via the wb parm), I'd use:

wb.custom...
instead of
ActiveWorkbook.Custom...
(for all those activeworkbook references)

If you want to stop the "are you sure you want to overwrite" prompts:

application.displayalerts = false 'added
Application.EnableEvents = False
wb.SaveAs wb.Path & "\" & wb.CustomDocumentProperties("DocName") & _
"_v" & wb.CustomDocumentProperties("MajNo") & "." & _
wb.CustomDocumentProperties("MinNo") & Ext
Application.EnableEvents = True
application.displayalerts = true 'added

(No need for parentheses around the filename in the .SaveAs line, either.)

But I don't think will stop excel from crashing.

I don't have a guess for that.

 

[JT Klipfer]

Thanks Dave,

Noted on the wb usage; actually, my requirements grew into a little more of
a robust functionality that I solved by implementing a custom user form.
When executing through that means, I don't seem to have any problems. Thanks
again for your help!--JT

 

[Dave Peterson]

I think that's a better approach, too.

I think making a dedicated macro/userform to do something like this makes a lot
more sense.