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

Indiana99

New Member
Joined
Nov 5, 2010
Messages
8
<TABLE id=post2502537 class=tborder border=0 cellSpacing=0 cellPadding=6 width="100%" align=center><TBODY><TR vAlign=top><TD style="BORDER-RIGHT: #ffffff 1px solid" id=td_post_2502537 class=alt1>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

<NOBR>Application</NOBR>.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 <NOBR>Income</NOBR> 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...
<!-- / message -->


</TD></TR><TR><TD style="BORDER-BOTTOM: #ffffff 1px solid; BORDER-LEFT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-RIGHT: #ffffff 1px solid" class=alt2>(Oops... sorry, I'm new here, just found the rules about cross posting
I've also posted this thread in the Technical Excel Issues forum - link below)

http://www.mrexcel.com/forum/showthread.php?t=507136

</TD><TD style="BORDER-BOTTOM: #ffffff 1px solid; BORDER-LEFT: #ffffff 0px solid; BORDER-TOP: #ffffff 0px solid; BORDER-RIGHT: #ffffff 1px solid" class=alt1 align=right><!-- controls --><!-- / controls --></TD></TR></TBODY></TABLE>
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Indiana99

New Member
Joined
Nov 5, 2010
Messages
8
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,985
Messages
5,767,444
Members
425,414
Latest member
chwein

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
Top