Has anyone ever encountered an error AFTER the macro has completed

[eluehmann]

I have the following code which is executed upon hitting "save" and whe
it is done running (when I step through it) I get the following error:

"Microsoft excel has encountered a problem and needs to close. We ar
sorry for the inconvenience. The info you were working on may be lost
Microsoft Office Excel can try to recover it for you..."

Then it has a check box for if you want to recover or not.

Here is the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean)
If Sheets("Documentation").Range("A100").Value = "SAVED" Then
Sheets("Documentation").Visible = 2
Exit Sub
Else
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")

x = MsgBox("Have you significantly changed the layout o
calculation of this workbook?", vbYesNo, "Sarbanes Oxley Complianc
Alert!")

If x = 6 Then
y = InputBox("Please decsribe the changes you have made", "SO
Change description")
If y = "" Then
Exit Sub
End If
Sheets("Documentation").Visible = -1
Sheets("Documentation").Select
Range("A4000").Select
Selection.End(xlUp).Select
r0w = ActiveCell.Row + 1
Sheets("Documentation").Range("A100").Value = "SAVED"
Sheets("Documentation").Range("A" & r0w).Value = Now()
Sheets("Documentation").Range("B" & r0w).Value = Left(Range("B
& r0w - 1).Value, Len(Range("B" & r0w - 1).Value) - 2) & "V"
Right(Range("B" & r0w - 1).Value, 1) + 1
Sheets("Documentation").Range("C" & r0w).Value
Environ("Username")
Sheets("Documentation").Range("D" & r0w).Value = y
ActiveWorkbook.SaveAs Filename:="\\finance\finance\SO
Compliant Excel Docs\" & Range("B" & r0w).Value
oldfile = "\\finance\finance\SOX Compliant Excel Docs\"
Range("B" & r0w - 1).Value & ".xls"
fso.movefile oldfile, "\\finance\finance\SOX Compliant Exce
Docs\Old Versions\"
Sheets("Documentation").Visible = 2
End If
End If
Sheets(2).Select
End Su

 

[RB Smissaert]

Before anything else I would start with declaring all your variables and
then see if it compiles.

RBS

 

[NickHK]

As well as RBS's advice, to make your code more readable, use the constants
that VBA/Excel expose; vbYes=6, xlVeyHidden=2 etc

You don't need the FSO just to move a file; look into Name and Kill in the
VBA help.
When you .SaveAs, does it have the .xls extension ?
You are not setting Cancel=True, so Excel is saving the file again, after
you code.

I suppose oldfile refers to the previous name of the file.
Why get that before the ,saveAs
oldfile=WB.Path & "\" & WB.Name
WB.SaveAS....

NickHK