Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/Match ... Excel has encountered an Error | Excel Discussion (Misc queries) | |||
Excel encountered an error | Excel Discussion (Misc queries) | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Extracting data from Access - error encountered | Excel Programming | |||
how to add message box after a macro is completed | Excel Programming |