Here is the code that I have written, for some reason I am unable to
get the Save As dialog box to appear, the spreadsheet just saves and
closes. Any suggestions?

Private Sub Workbook_BeforeClose(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Msg = "Do you want to save the expense report?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
ActiveWorkbook.SaveAs
Case vbNo
ActiveWorkbook.Close
End Select
End If

End Sub

you have not supplied a path & file name

example


ActiveWorkbook.SaveAs "c:\temp\test.xls

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

Public Sub SaveAs()
Dim Filename As String
Msg = "Do you want to save the expense report?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
Filename = InputBox("Enter File Name")
ActiveWorkbook.SaveAs Filename
Case vbNo
ActiveWorkbook.Close
End Select

End Sub

"Gun_Maddie" wrote in message
...
Here is the code that I have written, for some reason I am unable to
get the Save As dialog box to appear, the spreadsheet just saves and
closes. Any suggestions?

Private Sub Workbook_BeforeClose(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Msg = "Do you want to save the expense report?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
ActiveWorkbook.SaveAs
Case vbNo
ActiveWorkbook.Close
End Select
End If

End Sub

Public Sub SaveAs()
Dim Filename As String
Msg = "Do you want to save the expense report?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
Filename = InputBox("Enter File Name")
ActiveWorkbook.SaveAs Filename
Case vbNo
ActiveWorkbook.Close
End Select

End Sub

"Gun_Maddie" wrote in message
...
Here is the code that I have written, for some reason I am unable to
get the Save As dialog box to appear, the spreadsheet just saves and
closes. Any suggestions?

Private Sub Workbook_BeforeClose(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Msg = "Do you want to save the expense report?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
ActiveWorkbook.SaveAs
Case vbNo
ActiveWorkbook.Close
End Select
End If

End Sub

Gun,

Looks like you've got an extra End If where you don't need it.

Since you've got just two outcomes, vbYes or vbNo, why not replace Select
Case with a simple If statement?

eg.
Private Sub Workbook_BeforeClose(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If MsgBox("Do you want to save the expense report?", vbYesNo) = vbYes
Then ActiveWorkbook.SaveAs Else ActiveWorkbook.Close
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gun_Maddie" wrote in message
...
Here is the code that I have written, for some reason I am unable to
get the Save As dialog box to appear, the spreadsheet just saves and
closes. Any suggestions?

Private Sub Workbook_BeforeClose(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Msg = "Do you want to save the expense report?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
ActiveWorkbook.SaveAs
Case vbNo
ActiveWorkbook.Close
End Select
End If

End Sub

I neglected to mention I also would like to allow the user to be able
to select what drive and filename they wish to save the report to. Is
this possible? I thought that is what the SaveAsUI would bring up?
Maybe I am incorrect.

I forgot to mention that I disabled all commandbars (yes I am putting
them back), so selecting Save As on the file menu is not an option. I
would either like to use an activeworkbook.saveas or
activeworkbook.close to trigger the individual to save.