Saving And Checking

  • Thread starter Thread starter Bigjayce
  • Start date Start date
B

Bigjayce

Hi there,

I am writing a save and close button and this must be so easy I hav
tied myself in knots and cannot see what is wrong.

Basically I have created the button and am using th
Application.Dialogs(xlDialogSaveAs).Show command to bring up the sav
file box. after the filename is entered and the file saves th
Application.quit is activated and the spreadsheet closes.

Problem is, if you click the CANCEL button the spreadsheet still quit
so I need a way of checking to see if the spreadsheet has been save
and if not returning to the workbook, if it has been saved then quit.

Must be easy to do lol

I'm using Excel 97

Bigjayc
 
Bigjayce

Of course it's easy. Use the fact that the workbook's Saved
property is set to True when the workbook is saved.

Sub SaveWorkbook()
Dim x As Boolean

x = ThisWorkbook.Saved
ThisWorkbook.Saved = False
Application.Dialogs(xlDialogSaveAs).Show

If ThisWorkbook.Saved = True Then
MsgBox "It's been saved"
Else
MsgBox "No save"
ThisWorkbook.Saved = x
End If
End Sub
 
Instead of using the Dialogs(xlDialogSaveAs) method, use
Application.GetSaveAsFilename. Test the result of this, and if not False,
save and close the file. Note that GetSaveAsFilename doesn't actually save
the file -- it merely prompts the user for a file name.

Dim FName As Variant
FName = Application.GetSaveAsFilename(, "Excel Files (*.xls),*.xls")
If FName <> False Then
ThisWorkbook.SaveAs FName
Application.Quit
Else
' user cancelled, do nothing
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Thanks guys :)

I knew it wasn't difficult but I just got to the point where I couldn'
see the code anymore it was just funny dots on the screen lol

Bigjayc
 
Back
Top