<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>
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:
