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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

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

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

"Bigjayce" wrote in message
...

Hi there,

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

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

Problem is, if you click the CANCEL button the spreadsheet still quits
so I need a way of checking to see if the spreadsheet has been saved
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

Bigjayce


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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




"Bigjayce" wrote in message
...

Hi there,

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

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

Problem is, if you click the CANCEL button the spreadsheet still quits
so I need a way of checking to see if the spreadsheet has been saved
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

Bigjayce


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

You are welcome, Bigjayce!

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

"Bigjayce" wrote in message
...

Thanks guys :)

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

Bigjayce


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/