David,
That worked great, thanks! While waiting for this to get posted, I had come up with a routine that worked, where I created a custom close routine and passed it the cancel variable, then passed it back as true when it was finished which immediately closed the routine. It worked, but not as simply as yours. Sometimes I miss the obvious.
Scott
--- In ExcelVBA@yahoogroups.com, "David Grugeon" <yahoo@...> wrote:
>
> 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