SaveAsUI

[Gun_Maddie]

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

 

[mudraker]

you have not supplied a path & file name

example


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

 

[Dale Hymel]

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

 

[Dale Hymel]

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

 

[Rob van Gelder]

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

 

[Gun_Maddie]

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.

 

[Gun_Maddie]

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.