Excel crashing/hanging after simple vba commands, help please!!!

[Indiana99]

Hi everybody
I have an Excel 2007 worksheet with very simple macros, here are some of them as an example:

'-----------------------------------------------------

Sub AUTO_OPEN()
' Automatic loading to the Welcome screen & turns off toolbars, etc and displays the full screen.
' Keyboard shortcut: Ctrl+a

Application.DisplayAlerts = True
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayWorkbookTabs = False
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Sheets("Open").Select
Application.Goto Reference:="WELCOME_SCREEN"
MsgBox "Welcome to the Income and Expenses application.", vbOKOnly, "Mat Thew Pty Ltd"

End Sub

'-----------------------------------------------------

Sub Goto_Input()
' Goes to Input screen
' Keyboard Shortcut: Ctrl+i

Application.Goto Reference:="INPUT_SCREEN"
ActiveWindow.DisplayHeadings = False
If MsgBox("Would you like to clear the existing information?", vbQuestion + vbYesNo, "Income and Expenses") = vbYes Then
With Sheets("Input")
.Range("InputPeriodEnded").Value = Date
.Range("InputRentIncome").ClearContents
.Range("InputInterestIncome").ClearContents
.Range("InputCommissionpaid").ClearContents
.Range("InputWages").ClearContents
.Range("InputMaintenanceCosts").ClearContents
.Range("InputTelephones").ClearContents
.Range("InputElectricity").ClearContents
.Range("InputRates").ClearContents
.Range("InputRentPaid").ClearContents
.Range("InputOtherExpenses").ClearContents
End With
End If

End Sub

'-----------------------------------------------------

Sub Save_File()
' Saves file under current name
' Keyboard Shortcut: Ctrl+s

If MsgBox("Are you sure you want to save the file under its current name?", vbQuestion + vbYesNo, "Income and Expenses") = vbNo Then
Exit Sub
End If
ActiveWorkbook.Save
MsgBox "The file has been saved.", vbOKOnly + vbInformation, "Income and Expenses"

End Sub

'-----------------------------------------------------

One of the macros also calls up a form for data entry.

Two problems are driving me crazy!!
They are mentioned in several internet forums but I haven't found a solution that works for me.

FIRST PROBLEM:
=============

After printing, the macro comes back to the expected worksheet, but:
- charts & command buttons are not displayed (even though they are still there, I can select them, even click on the buttons and they will work)
- when trying to close the workbook, Excel starts madly refreshing the screen (charts and buttons can be seen and then disappear again in an
infinte loop) and nothing else happens. It doesn't even respond to Ctrl-Break to interrupt the macro

If I comment the following line

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

then the problem doesn't happen, but obviously that's not a viable solution as I need to print!
I get the same problem with other commands such as PrintPreviw and SetPrintArea

Here is the macro:

Sub Print_Output()
' Prints report and charts
' Keyboard Shortcut: Ctrl+p

If Sheets("Input").Range("InputPeriodEnded").Value = "" Then
MsgBox "There is no date for the end of the period." & Chr(13) & "Please go back to Input screen and enter a date.", vbOKOnly + vbInformation, "Income and Expenses"
Exit Sub
End If

Sheets("Output").Select
Application.ScreenUpdating = False

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&D"
.CenterFooter = "&T"
.RightFooter = "&F"
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Application.Goto Reference:="OUTPUT_SCREEN"
Application.ScreenUpdating = True

End Sub


SECOND PROBLEM:
==============

The Auto_Close routine...

It runs well when executed thorugh Macros / Run macro
It runs well when closing the worksheet by clicking on the 'x' button
It runs well when using the shortcut Ctrl-e associated to a command button on the worksheet
But when actually clicking on that command button, it runs and does everything as expected, but immediately afterwards Excel crashes.

It also works well when instead of a form button I use an Activex button, but this shouldn't make any difference!

Here is the procedure

Sub AUTO_CLOSE()
' Event procedure called before closing workbook
' Keyboard Shortcut: Ctrl+e

If Not ActiveWorkbook.Saved Then
If MsgBox("Are you sure you want to close without saving?", vbQuestion + vbYesNo, "Income and Expenses") = vbNo Then
Exit Sub
End If
End If
MsgBox "The file will now close without saving.", vbOKOnly + vbInformation, "Income and Expenses"
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayWorkbookTabs = True
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
'Workbooks("MatThew.xlsm").Close savechanges:=False
ActiveWorkbook.Close savechanges:=False

End Sub


I'm at my wits' end! Any help will be appreciated...

 

[Indiana99]

(Oops... sorry, I'm new here, just found out the rules about cross posting
I've also posted this thread in the 'All Excel /VBA questions' forum - link below)
http://www.mrexcel.com/forum/showthread.php?t=507153

 

[Indiana99]

And also
http://www.excelforum.com/excel-pro...ng-after-simple-vba-commands-help-please.html

 

[Derek Brown]

Is there a particular reason why you are using an'AutoClose' macro instead of having that code in the Workbook_BeforeClose event where the 'Cancel' argument can be used to abort the close if necessary?
It looks as though you are closing the workbook twice? The comment (' Event procedure called before closing workbook) indicates that your macro, called during the close process actually closes the workbook before the rest of the code is processed. Have you tried removing that line of code?
Regarding the printing problem, have you tried recording a macro to see the code that is generated? Excel 2007 does not have a very good macro recorded, so it may not help.

 

[Indiana99]

Hi Derek
I've tried the Workbook_Before close with the Cancel argument, and had the exact same problem. My most recent version has the AutoClose option but I get the problem regardless.
What puzzles me is that the macro works perfectly fine if I go through Macro/Run, shortcut Ctrl-E, and even when attached to an ActiveX button, so you would think that it should work when attached to a 'normal' form button?
This puzzling behaviour for VBA seems to be quite common judging by the number of threads about it.
I'll try the recording option for the printout as soon as I can get access to a computer with Excel 2007... I have to rely on the public library PCs!
Everything works OK if I comment the PrintOut line though, and I've tried several versions for this line as well. They all print all right but then the charts disappear and Excel starts an infinite loop when trying to run the next macro.
I admit my knowledge of VBA is very basic but what I'm trying to do is so simple that I can't see how it can go wrong... aren't I clever, I manage to make all charts and macro buttons disappear just with a PrintOut command!
Thanks for your help, and I like your anger quote by the way!

 

[Indiana99]

OK sorry forget about it, I've given up! Converted the whole thing to Excel 2003 and it worked straightaway.
Tried to close the thread but not sure how