SaveAs Dialog Box

[Guest]

Hi
A message box pop-ups before the workbook closes to see if the user wishes
to save the workbook.

If the user chooses Yes then I would like the SaveAs dialog box to pop-up so
they can choose the location they wish to save the workbook and with whatever
name they wish.

Just for background purposes, before the user has this option the workbook
is saved to their C drive so we can give it a common name for email purposes.
Once the form has been emailed that is when I would like to give the user an
option of savign the workbook to a drive, folder and name of their chose.

Thanks
Noemi

 

[Bob Phillips]

Maybe this will work for you

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sFilename
If MsgBox("Save the file?", vbYesNo) = vbYes Then
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel File (*.xls), *.xls")
If sFilename <> False Then
ThisWorkbook.SaveAs sFilename
End If
End If
End Sub

Private Sub Workbook_Open()
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)