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


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


' 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
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
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
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.


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

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


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

' 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...

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Derek Brown

Well-known Member
Dec 26, 2005
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.
Upvote 0


New Member
Nov 5, 2010
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!
Upvote 0


New Member
Nov 5, 2010
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
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back