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:

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
 

Forum statistics

Threads
1,081,841
Messages
5,361,638
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top