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