Last week I asked a question as how to get the save as dialog box to
appear. I finally was able to get it to appear. I was wondering what
if someone hits the cancel button in the dialog box, ie they decide
they need to continue to work. Here is the code I have written so
far. My problem is that the workbook is closing if I hit the cancel
button in the save as dialog box. What I would like to happen is a
message box to appear and ask if they would like to close without
saving.

Application.DisplayAlerts = False
' Brings up the Save As Dialog Box
Msg = "Would you like to save the expense report?"
Ans = MsgBox(Msg, vbQuestion + vbYesNo)
Select Case Ans
Case vbYes
Application.Dialogs(xlDialogSaveAs).Show
Call ShowToolBars
ActiveWorkbook.Close
' If an employee hits the cancel button from the Save As Dialog Box
If Cancel = True Then
Answer = MsgBox("Do you want to close the workbook _
without saving?", vbQuestion + vbYesNo)
Select Case Answer
Case vbYes
Call ShowToolBars
ActiveWorkbook.Close
Case vbNo
Range("B15").Select
End Select
End If
Case vbNo
Call ShowToolBars
ActiveWorkbook.Close
End Select
End Sub

Hi,

I noticed that you have used the code: ActiveWorkbook.close.

Try using this instead:

Application.quit

This will totally close the workbook without prompting for a save, an
withoput saving any open workbooks. You might want to put in a warnin
about that in your MsgBox.

Cheer

--
Message posted from http://www.ExcelForum.com

Joseph wrote in message ...
Hi,

I noticed that you have used the code: ActiveWorkbook.close.

Try using this instead:

Application.quit

This will totally close the workbook without prompting for a save, and
withoput saving any open workbooks. You might want to put in a warning
about that in your MsgBox.

Cheers


---
Message posted from http://www.ExcelForum.com/

I guess what I am trying to ask is how can I get a message box to
appear if someone clicks the cancel button in the save as dialog box?
With the code above if I click on cancel the program quits, but I
would like a dialog box to appear asking if they would like to close
the program.

Thanks

Untested, but consider this:

Ans = MsgBox(Msg, vbQuestion + vbYesNo)
Select Case Ans
Case vbYes
Application.Dialogs(xlDialogSaveAs).Show
Call ShowToolBars
ActiveWorkbook.Close
' If an employee hits the cancel button from the Save As
Dialog Box

Case Else

Answer = MsgBox("Do you want to close the
workbook _
without saving?", vbQuestion + vbYesNo)
Select Case Answer
Case vbYes
Call ShowToolBars
ActiveWorkbook.Close
Case vbNo
Range("B15").Select
exit sub
Case Else
Range("B15").Select
exit sub
End Select







-----Original Message-----
Last week I asked a question as how to get the save as
dialog box to
appear. I finally was able to get it to appear. I was
wondering what
if someone hits the cancel button in the dialog box, ie
they decide
they need to continue to work. Here is the code I have
written so
far. My problem is that the workbook is closing if I hit
the cancel
button in the save as dialog box. What I would like to
happen is a
message box to appear and ask if they would like to close
without
saving.

Application.DisplayAlerts = False
' Brings up the Save As Dialog Box
Msg = "Would you like to save the expense report?"
Ans = MsgBox(Msg, vbQuestion + vbYesNo)
Select Case Ans
Case vbYes
Application.Dialogs(xlDialogSaveAs).Show
Call ShowToolBars
ActiveWorkbook.Close
' If an employee hits the cancel button from the Save As
Dialog Box
If Cancel = True Then
Answer = MsgBox("Do you want to close the
workbook _
without saving?", vbQuestion + vbYesNo)
Select Case Answer
Case vbYes
Call ShowToolBars
ActiveWorkbook.Close
Case vbNo
Range("B15").Select
End Select
End If
Case vbNo
Call ShowToolBars
ActiveWorkbook.Close
End Select
End Sub
.