Hi Scott
You do have a loop and this is what is making it seem as though it crashes
excel.
In the "If Not Cancel" bit you enable events then close which will call the
before close macro again. The msgbox will redisplay and there is nothing
you can do (because it is modular) except go on clicking it.
There is a cure!
Create a global variable called "InCloseMacro" You can put it at the top of
your module after the Option Explicit and before the first sub
Dim InCloseMacro as Boolean
Then as the first two lines of the Before Close macro put
If InCloseMacro Then Exit Sub
InCloseMacro = true
Also before Cancel=True put
InCloseMacro = false
And you can take out your enableEvents statements.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of Scott
Sent: Tuesday, 4 October 2011 3:47 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Problem with my OnClose routine
I'm trying to create a workbook that has one sheet open when macros are not
enabled, and another when they are. I thought I had it working very well,
until I tried it when another workbook is open at the same time. When there
are more than one workbook open and I try to close without saving my file,
it crashes Excel. This only crashes when I close without saving my changes.
Can anyone offer a suggestion on what is going on and how to fix it. Here
is all my code that resides in the 'ThisWorkbook' object:
'----------------------CODE STARTS HERE------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Turn off events to
prevent unwanted loops
'Application.EnableEvents = False
'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to
'" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Hide all sheets
Call HideAllSheets
ThisWorkbook.Save
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If
'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
'Application.EnableEvents = True
.Saved = True
.Close savechanges:=False
End If
End With
'Application.EnableEvents = True
End Sub
'*******************
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim varWorkbookName As String
Dim FileFormatValue As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
'Forces save as .XLSM file type.
Call HideAllSheets
If SaveAsUI = True Then
varWorkbookName = Application.GetSaveAsFilename( _
fileFilter:="Excel Macro Enabled Workbook
(*.xlsm), *.xlsm")
Cancel = True
If varWorkbookName <> "False" Then
Select Case LCase(Right(varWorkbookName, Len(varWorkbookName) -
InStrRev(varWorkbookName, ".", , 1)))
Case "xlsm": FileFormatValue = 52
End Select
ActiveWorkbook.SaveAs varWorkbookName
End If
Else
ThisWorkbook.Save
End If
Call ShowAllSheets
Sheet1.Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
'*******************
Private Sub Workbook_Open()
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMaximized
Sheet1.Visible = True 'Form sheet
Sheet1.Activate
Sheet3.Visible = xlSheetVeryHidden 'No Macros Sheet
Sheet2.Visible = xlSheetHidden 'Defaults
Sheet4.Visible = xlSheetHidden 'Data_Fields
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'*******************
Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Sheet3.Visible = xlSheetVisible 'Macros Not Enabled
Sheet1.Visible = xlSheetVeryHidden 'Item Upload
Sheet2.Visible = xlSheetVeryHidden 'Defaults
Sheet4.Visible = xlSheetVeryHidden 'Data Fields
Sheet3.Activate
'-----------------------CODE ENDS-------------------------------------
Sheet 1 is the sheet that opens when Macros are enabled. Sheet 3 is the
sheet that opens when they are not.
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar