Closing Excel without saving

Hi
Attached is a procedure I wrote to close excel when a certain criteria was
not met. However I still get the default message box "Save changes". I want
the procedure to select the 'Don't Save' option and then Quit Excel. Any help
would be welcome.

Private Sub OptionButton6_Click()
Dim Response As String
Dim msg As String
Dim Style As String

msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
available to you. Do you want to apply for Study Leave Only(Level One
Support)?"
Style = vbYesNo
Response = MsgBox(msg, Style)
If Response = vbNo Then
MsgBox "You will be logged out"
ActiveWorkbook.Close savechanges:=False
Application.Quit
Else
Range("b10.j10").Select
Endif
Endsub

It seems reasonble to close the workbook without saving if you want. But
closing the application seems kind of harsh to me.

If I have several other workbooks open and not saved (or that I don't want
closed), why should you close them?

But if you want...

If Response = vbNo Then
MsgBox "You will be logged out"
Application.DisplayAlerts = False
Application.Quit
'application.displayalerts = true
'thisworkbook.close savechanges:=false
Else
Range("b10.j10").Select
End if

Everything after the .quit (within that THEN portion) isn't necessary. If you
close excel, then the macro that used to be running ain't running anymore.

I wouldn't do this.

And if I did, I'd run away from that big guy who spent all morning working on an
important project and didn't save!


Abs wrote:
>
> Hi
> Attached is a procedure I wrote to close excel when a certain criteria was
> not met. However I still get the default message box "Save changes". I want
> the procedure to select the 'Don't Save' option and then Quit Excel. Any help
> would be welcome.
>
> Private Sub OptionButton6_Click()
> Dim Response As String
> Dim msg As String
> Dim Style As String
>
> msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
> available to you. Do you want to apply for Study Leave Only(Level One
> Support)?"
> Style = vbYesNo
> Response = MsgBox(msg, Style)
> If Response = vbNo Then
> MsgBox "You will be logged out"
> ActiveWorkbook.Close savechanges:=False
> Application.Quit
> Else
> Range("b10.j10").Select
> Endif
> Endsub

--

Dave Peterson

Dave, Thanks, I should not be quiting excel completely, just the current
workbook. How should my code look like then?

"Dave Peterson" wrote:

> It seems reasonble to close the workbook without saving if you want. But
> closing the application seems kind of harsh to me.
>
> If I have several other workbooks open and not saved (or that I don't want
> closed), why should you close them?
>
> But if you want...
>
> If Response = vbNo Then
> MsgBox "You will be logged out"
> Application.DisplayAlerts = False
> Application.Quit
> 'application.displayalerts = true
> 'thisworkbook.close savechanges:=false
> Else
> Range("b10.j10").Select
> End if
>
> Everything after the .quit (within that THEN portion) isn't necessary. If you
> close excel, then the macro that used to be running ain't running anymore.
>
> I wouldn't do this.
>
> And if I did, I'd run away from that big guy who spent all morning working on an
> important project and didn't save!
>
>
> Abs wrote:
> >
> > Hi
> > Attached is a procedure I wrote to close excel when a certain criteria was
> > not met. However I still get the default message box "Save changes". I want
> > the procedure to select the 'Don't Save' option and then Quit Excel. Any help
> > would be welcome.
> >
> > Private Sub OptionButton6_Click()
> > Dim Response As String
> > Dim msg As String
> > Dim Style As String
> >
> > msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
> > available to you. Do you want to apply for Study Leave Only(Level One
> > Support)?"
> > Style = vbYesNo
> > Response = MsgBox(msg, Style)
> > If Response = vbNo Then
> > MsgBox "You will be logged out"
> > ActiveWorkbook.Close savechanges:=False
> > Application.Quit
> > Else
> > Range("b10.j10").Select
> > Endif
> > Endsub
>
> --
>
> Dave Peterson
>

I would think that this would be sufficient.

If Response = vbNo Then
MsgBox "You will be logged out"
thisworkbook.close savechanges:=false
Else
Range("b10.j10").Select
End if

As soon as the workbook closes, the macro still ends, too.

Abs wrote:
>
> Dave, Thanks, I should not be quiting excel completely, just the current
> workbook. How should my code look like then?
>
> "Dave Peterson" wrote:
>
> > It seems reasonble to close the workbook without saving if you want. But
> > closing the application seems kind of harsh to me.
> >
> > If I have several other workbooks open and not saved (or that I don't want
> > closed), why should you close them?
> >
> > But if you want...
> >
> > If Response = vbNo Then
> > MsgBox "You will be logged out"
> > Application.DisplayAlerts = False
> > Application.Quit
> > 'application.displayalerts = true
> > 'thisworkbook.close savechanges:=false
> > Else
> > Range("b10.j10").Select
> > End if
> >
> > Everything after the .quit (within that THEN portion) isn't necessary. If you
> > close excel, then the macro that used to be running ain't running anymore.
> >
> > I wouldn't do this.
> >
> > And if I did, I'd run away from that big guy who spent all morning working on an
> > important project and didn't save!
> >
> >
> > Abs wrote:
> > >
> > > Hi
> > > Attached is a procedure I wrote to close excel when a certain criteria was
> > > not met. However I still get the default message box "Save changes". I want
> > > the procedure to select the 'Don't Save' option and then Quit Excel. Any help
> > > would be welcome.
> > >
> > > Private Sub OptionButton6_Click()
> > > Dim Response As String
> > > Dim msg As String
> > > Dim Style As String
> > >
> > > msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
> > > available to you. Do you want to apply for Study Leave Only(Level One
> > > Support)?"
> > > Style = vbYesNo
> > > Response = MsgBox(msg, Style)
> > > If Response = vbNo Then
> > > MsgBox "You will be logged out"
> > > ActiveWorkbook.Close savechanges:=False
> > > Application.Quit
> > > Else
> > > Range("b10.j10").Select
> > > Endif
> > > Endsub
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave, I tried. I have MsOffice2003, Excel ver 11.0
The system returns with a default message box heading Save Changes. In the
box there are four options, First two are two different drives, the third
option is 'Don't save' and the last option is 'cancel'. I wanted to pick the
default option as 'Don't Save' and close workbook.
A subroutine similar to your suggestion in my PC at home works fine. Is it
that the IT dep't here locally customised this default "Save Changes" msgbox?

"Abs" wrote:

> Dave, Thanks, I should not be quiting excel completely, just the current
> workbook. How should my code look like then?
>
> "Dave Peterson" wrote:
>
> > It seems reasonble to close the workbook without saving if you want. But
> > closing the application seems kind of harsh to me.
> >
> > If I have several other workbooks open and not saved (or that I don't want
> > closed), why should you close them?
> >
> > But if you want...
> >
> > If Response = vbNo Then
> > MsgBox "You will be logged out"
> > Application.DisplayAlerts = False
> > Application.Quit
> > 'application.displayalerts = true
> > 'thisworkbook.close savechanges:=false
> > Else
> > Range("b10.j10").Select
> > End if
> >
> > Everything after the .quit (within that THEN portion) isn't necessary. If you
> > close excel, then the macro that used to be running ain't running anymore.
> >
> > I wouldn't do this.
> >
> > And if I did, I'd run away from that big guy who spent all morning working on an
> > important project and didn't save!
> >
> >
> > Abs wrote:
> > >
> > > Hi
> > > Attached is a procedure I wrote to close excel when a certain criteria was
> > > not met. However I still get the default message box "Save changes". I want
> > > the procedure to select the 'Don't Save' option and then Quit Excel. Any help
> > > would be welcome.
> > >
> > > Private Sub OptionButton6_Click()
> > > Dim Response As String
> > > Dim msg As String
> > > Dim Style As String
> > >
> > > msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
> > > available to you. Do you want to apply for Study Leave Only(Level One
> > > Support)?"
> > > Style = vbYesNo
> > > Response = MsgBox(msg, Style)
> > > If Response = vbNo Then
> > > MsgBox "You will be logged out"
> > > ActiveWorkbook.Close savechanges:=False
> > > Application.Quit
> > > Else
> > > Range("b10.j10").Select
> > > Endif
> > > Endsub
> >
> > --
> >
> > Dave Peterson
> >

I don't recognize that dialog and I also use xl2003.

My iniitial bet was you didn't include the
application.displayalerts = false
line.

But I'm not sure. You may want to post the relevant code.

Abs wrote:
>
> Dave, I tried. I have MsOffice2003, Excel ver 11.0
> The system returns with a default message box heading Save Changes. In the
> box there are four options, First two are two different drives, the third
> option is 'Don't save' and the last option is 'cancel'. I wanted to pick the
> default option as 'Don't Save' and close workbook.
> A subroutine similar to your suggestion in my PC at home works fine. Is it
> that the IT dep't here locally customised this default "Save Changes" msgbox?
>
> "Abs" wrote:
>
> > Dave, Thanks, I should not be quiting excel completely, just the current
> > workbook. How should my code look like then?
> >
> > "Dave Peterson" wrote:
> >
> > > It seems reasonble to close the workbook without saving if you want. But
> > > closing the application seems kind of harsh to me.
> > >
> > > If I have several other workbooks open and not saved (or that I don't want
> > > closed), why should you close them?
> > >
> > > But if you want...
> > >
> > > If Response = vbNo Then
> > > MsgBox "You will be logged out"
> > > Application.DisplayAlerts = False
> > > Application.Quit
> > > 'application.displayalerts = true
> > > 'thisworkbook.close savechanges:=false
> > > Else
> > > Range("b10.j10").Select
> > > End if
> > >
> > > Everything after the .quit (within that THEN portion) isn't necessary. If you
> > > close excel, then the macro that used to be running ain't running anymore.
> > >
> > > I wouldn't do this.
> > >
> > > And if I did, I'd run away from that big guy who spent all morning working on an
> > > important project and didn't save!
> > >
> > >
> > > Abs wrote:
> > > >
> > > > Hi
> > > > Attached is a procedure I wrote to close excel when a certain criteria was
> > > > not met. However I still get the default message box "Save changes". I want
> > > > the procedure to select the 'Don't Save' option and then Quit Excel. Any help
> > > > would be welcome.
> > > >
> > > > Private Sub OptionButton6_Click()
> > > > Dim Response As String
> > > > Dim msg As String
> > > > Dim Style As String
> > > >
> > > > msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
> > > > available to you. Do you want to apply for Study Leave Only(Level One
> > > > Support)?"
> > > > Style = vbYesNo
> > > > Response = MsgBox(msg, Style)
> > > > If Response = vbNo Then
> > > > MsgBox "You will be logged out"
> > > > ActiveWorkbook.Close savechanges:=False
> > > > Application.Quit
> > > > Else
> > > > Range("b10.j10").Select
> > > > Endif
> > > > Endsub
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson

Dave, thanks for keeping up with me. The attached code as in the workbook.
You can see it is attached to an option button. Obviously there are some
changes to the original workbook when a user clicks on the option button.
Thus I am trying to close the workbook without saving any changes. With this
code I continue to get the systems default message which says, "the workbook
has been modified, Where do you want to save the changes?" One of the options
is 'Don't Save' and manually I can click on it and everything is fine.
However, I want to automatically close the workbook without the user having
to click on the 'Don't Close' button.

Private Sub OptionButton6_Click()
Dim Response As String
Dim msg As String
Dim Style As String

msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
available to you. Do you want to apply for Study Leave Only(Level One
Support)?"
Style = vbYesNo
Response = MsgBox(msg, Style)
If Response = vbNo Then
MsgBox "You will be logged out"
ActiveWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=False
Application.DisplayAlerts = False
Else
Range("b10.j10").Select
End If
End Sub

Regards

This isn't a standard warning message in my USA version.

It kind of sounds like you have another macro running that's doing things.

I'd look under the ThisWorkbook module for Workbook_beforesave (or in any
general module for Auto_Close).



Abs wrote:
>
> Dave, thanks for keeping up with me. The attached code as in the workbook.
> You can see it is attached to an option button. Obviously there are some
> changes to the original workbook when a user clicks on the option button.
> Thus I am trying to close the workbook without saving any changes. With this
> code I continue to get the systems default message which says, "the workbook
> has been modified, Where do you want to save the changes?" One of the options
> is 'Don't Save' and manually I can click on it and everything is fine.
> However, I want to automatically close the workbook without the user having
> to click on the 'Don't Close' button.
>
> Private Sub OptionButton6_Click()
> Dim Response As String
> Dim msg As String
> Dim Style As String
>
> msg = "Financial Support (Level 2 as in Studybank Guidelined) is not
> available to you. Do you want to apply for Study Leave Only(Level One
> Support)?"
> Style = vbYesNo
> Response = MsgBox(msg, Style)
> If Response = vbNo Then
> MsgBox "You will be logged out"
> ActiveWorkbook.Saved = True
> ActiveWorkbook.Close savechanges:=False
> Application.DisplayAlerts = False
> Else
> Range("b10.j10").Select
> End If
> End Sub
>
> Regards

--

Dave Peterson

Hi Dave, it is me again.
I figured out the initial problem, just placing the displayalert code at the
right place, now saves my worksheet okay. However please look at the code
attached, after the Endif statement, when it tries to send for review, it
wants to save a shared version and I cannot make it pick option 'No'. I do
not want to create a shared version. If I deactivate the displayalert, the
code stops for an input from user on the Save As message box.
Sorry about this, but all this is arising from the fact my company uses
Lotus Notes as e-mail. I know Outlook does has this e-mail facility easier
than Lotus Notes.

Private Sub CommandButton1_Click()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

msg = "This workbook will close after saving; Are you sure to proceed?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
sPath = "u:\"
sFilename = Range("b26").Value
ans = MsgBox("File will be saved as " & sPath & sFilename &
"_Studybank Application")
Application.Goto Reference:=Worksheets("Page1").Range("f2")
Selection.Value = sFilename & " application"
Application.Goto Reference:=Worksheets("Page1").Range("i2")
Selection.Formula = Now()
Application.Goto Reference:=Worksheets("Page1").Range("a1")
ActiveWorkbook.SaveAs sPath & sFilename & ("_Studybank Application")
Else
MsgBox ("Please save your application")
End If
Application.DisplayAlerts = False
ActiveWorkbook.SendForReview _
Recipients:="; ", _
Subject:=sFilename & "-Studybank Application.", _
ShowMessage:=True, _
IncludeAttachment:=True
ActiveWorkbook.Close savechanges:=False
Endsub

I've never used the .sendforreview.

Maybe someone who knows how that works will jump in with a tip for you.

But until they do, maybe you could just share the workbook yourself with a line
of code like:

ActiveWorkbook.SaveAs sPath & sFilename & ("_Studybank Application"), _
accessmode:=xlShared

Since it'll be already shared, the user shouldn't have to do anything (not
vigorously tested, though).

Abs wrote:
>
> Hi Dave, it is me again.
> I figured out the initial problem, just placing the displayalert code at the
> right place, now saves my worksheet okay. However please look at the code
> attached, after the Endif statement, when it tries to send for review, it
> wants to save a shared version and I cannot make it pick option 'No'. I do
> not want to create a shared version. If I deactivate the displayalert, the
> code stops for an input from user on the Save As message box.
> Sorry about this, but all this is arising from the fact my company uses
> Lotus Notes as e-mail. I know Outlook does has this e-mail facility easier
> than Lotus Notes.
>
> Private Sub CommandButton1_Click()
> Dim Response As String
> Dim msg As String
> Dim Style As String
> Dim sPath As String
> Dim sFilename As String
> Dim ans
>
> msg = "This workbook will close after saving; Are you sure to proceed?"
> Style = vbYesNo + vbInformation + vbDefaultButton2
>
> Response = MsgBox(msg, Style)
> If Response = vbYes Then
> sPath = "u:\"
> sFilename = Range("b26").Value
> ans = MsgBox("File will be saved as " & sPath & sFilename &
> "_Studybank Application")
> Application.Goto Reference:=Worksheets("Page1").Range("f2")
> Selection.Value = sFilename & " application"
> Application.Goto Reference:=Worksheets("Page1").Range("i2")
> Selection.Formula = Now()
> Application.Goto Reference:=Worksheets("Page1").Range("a1")
> ActiveWorkbook.SaveAs sPath & sFilename & ("_Studybank Application")
> Else
> MsgBox ("Please save your application")
> End If
> Application.DisplayAlerts = False
> ActiveWorkbook.SendForReview _
> Recipients:="; ", _
> Subject:=sFilename & "-Studybank Application.", _
> ShowMessage:=True, _
> IncludeAttachment:=True
> ActiveWorkbook.Close savechanges:=False
> Endsub

--

Dave Peterson