E
Ed from AZ
I'm attempting to reset my workbook on close, but I've introduced
something that prevents Excel from quitting and all workbooks from
closing if I click the application "X" with more than one workbook
open.
My workbook opens on a sheet called "Splash" - if macros are disabled,
there's a message there giving the user instructions on how to use the
workbook. After the user is finished with all activity and closes the
book or quits the program, I want to reset the book back to the Splash
page.
My Personal workbook always opens underneath any Excel session because
I have macros in it. I do not have a BeforeClose macro in Personal.
If I only have thsi workbook and Personal open, everything closes and
quits fine. But if I have one more workbook open, the BeforeClose is
processed and the book opens the Splash sheet, but it won't close and
the application won't quit. Everything just sits there. If I hit the
"X" again, then everything closes.
The purpose of the If statement in the beginning of the code is to
allow the user to get partway into the book, then decide he doesn't
want to continue and quit, and present him with an opportunity to save
where he's at in his processes. I know the message box at the end
always pops up, but I wanted to give the user two chances (if you knew
my users, you'd understand!).
If someone can help me understand what I did to stop everything from
closing and how to do it right, I'd appreciate it.
Ed
Sub Workbook_BeforeClose(Cancel As Boolean)
'Stop
If Me.Saved = False Then
If MsgBox("Do you want to save your changes?", vbYesNo, "Save
Changes To This File?") = vbYes Then
Me.Save
Else
Exit Sub
End If
End If
With Me.Worksheets("Splash")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
Dim wks As Worksheet
For Each wks In Me.Worksheets
If wks.Name <> "Splash" Then wks.Visible = xlSheetHidden
Next wks
Me.Worksheets("Splash").Range("A1").Select
Me.Save
End Sub
something that prevents Excel from quitting and all workbooks from
closing if I click the application "X" with more than one workbook
open.
My workbook opens on a sheet called "Splash" - if macros are disabled,
there's a message there giving the user instructions on how to use the
workbook. After the user is finished with all activity and closes the
book or quits the program, I want to reset the book back to the Splash
page.
My Personal workbook always opens underneath any Excel session because
I have macros in it. I do not have a BeforeClose macro in Personal.
If I only have thsi workbook and Personal open, everything closes and
quits fine. But if I have one more workbook open, the BeforeClose is
processed and the book opens the Splash sheet, but it won't close and
the application won't quit. Everything just sits there. If I hit the
"X" again, then everything closes.
The purpose of the If statement in the beginning of the code is to
allow the user to get partway into the book, then decide he doesn't
want to continue and quit, and present him with an opportunity to save
where he's at in his processes. I know the message box at the end
always pops up, but I wanted to give the user two chances (if you knew
my users, you'd understand!).
If someone can help me understand what I did to stop everything from
closing and how to do it right, I'd appreciate it.
Ed
Sub Workbook_BeforeClose(Cancel As Boolean)
'Stop
If Me.Saved = False Then
If MsgBox("Do you want to save your changes?", vbYesNo, "Save
Changes To This File?") = vbYes Then
Me.Save
Else
Exit Sub
End If
End If
With Me.Worksheets("Splash")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
Dim wks As Worksheet
For Each wks In Me.Worksheets
If wks.Name <> "Splash" Then wks.Visible = xlSheetHidden
Next wks
Me.Worksheets("Splash").Range("A1").Select
Me.Save
End Sub