Has anyone ever encountered an error AFTER the macro has completed
 

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

--
eluehman
-----------------------------------------------------------------------
eluehmann's Profile: http://www.excelforum.com/member.php...fo&userid=1309
View this thread: http://www.excelforum.com/showthread.php?threadid=57048

Has anyone ever encountered an error AFTER the macro has completed
 

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

RBS

"eluehmann" wrote
in message ...

I have the following code which is executed upon hitting "save" and when
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 are
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 As
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 or
calculation of this workbook?", vbYesNo, "Sarbanes Oxley Compliancy
Alert!")

If x = 6 Then
y = InputBox("Please decsribe the changes you have made", "SOX
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\SOX
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 Excel
Docs\Old Versions\"
Sheets("Documentation").Visible = 2
End If
End If
Sheets(2).Select
End Sub


--
eluehmann
------------------------------------------------------------------------
eluehmann's Profile:
http://www.excelforum.com/member.php...o&userid=13095
View this thread: http://www.excelforum.com/showthread...hreadid=570487

Has anyone ever encountered an error AFTER the macro has completed
 

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

"eluehmann" wrote
in message ...

I have the following code which is executed upon hitting "save" and when
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 are
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 As
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 or
calculation of this workbook?", vbYesNo, "Sarbanes Oxley Compliancy
Alert!")

If x = 6 Then
y = InputBox("Please decsribe the changes you have made", "SOX
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\SOX
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 Excel
Docs\Old Versions\"
Sheets("Documentation").Visible = 2
End If
End If
Sheets(2).Select
End Sub


--
eluehmann
------------------------------------------------------------------------
eluehmann's Profile:
http://www.excelforum.com/member.php...o&userid=13095
View this thread: http://www.excelforum.com/showthread...hreadid=570487