save on exit message box
Is their a way to display a message box, when the user click to exit
the worksheet using a macro? The user will have 3 options Yes, No, Cancel. How can I program this using VBA in Ms Excel XP? ------------------------------------------------ The code I use at the moment saves the worksheet without asking the user: Sub CloseSpreedsheet() 'Saves spreedsheet ThisWorkbook.Saved = True 'Closes the application Application.Quit End Sub |
save on exit message box
Olly,
First of all, the line of code ThisWorkbook.Saved = True does NOT save the workbook. It tells Excel that the workbook does not need to be saved, but does NOT actually save any changes. Try something like the following: Sub CloseSpreedsheet() Dim Res As Long Res = MsgBox("Do you want to save?", vbYesNoCancel) Select Case Res Case vbYes ThisWorkbook.Save Case vbNo ThisWorkbook.Saved = True Case vbCancel Exit Sub End Select Application.Quit End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Olly" wrote in message om... Is their a way to display a message box, when the user click to exit the worksheet using a macro? The user will have 3 options Yes, No, Cancel. How can I program this using VBA in Ms Excel XP? ------------------------------------------------ The code I use at the moment saves the worksheet without asking the user: Sub CloseSpreedsheet() 'Saves spreedsheet ThisWorkbook.Saved = True 'Closes the application Application.Quit End Sub |
save on exit message box
If you want to save the workbook automatically then the
code is ThisWorkbook.Save On Excel 97 at least, is the default to ask users if they want to save changes before closing a workbook. If you want to produce this message box yourself, you could try Private Sub Workbook_BeforeClose(Cancel As Boolean) Select Case MsgBox("Want to SAve", vbQuestion +YesNoCancel) Case vbYes ThisWorkbook.Save Case vbNo ThisWorkbook.Saved = True Case vbCancel Cancel = True End Select End Sub libby -----Original Message----- Olly, First of all, the line of code ThisWorkbook.Saved = True does NOT save the workbook. It tells Excel that the workbook does not need to be saved, but does NOT actually save any changes. Try something like the following: Sub CloseSpreedsheet() Dim Res As Long Res = MsgBox("Do you want to save?", vbYesNoCancel) Select Case Res Case vbYes ThisWorkbook.Save Case vbNo ThisWorkbook.Saved = True Case vbCancel Exit Sub End Select Application.Quit End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Olly" wrote in message . com... Is their a way to display a message box, when the user click to exit the worksheet using a macro? The user will have 3 options Yes, No, Cancel. How can I program this using VBA in Ms Excel XP? ------------------------------------------------ The code I use at the moment saves the worksheet without asking the user: Sub CloseSpreedsheet() 'Saves spreedsheet ThisWorkbook.Saved = True 'Closes the application Application.Quit End Sub . |
save on exit message box
Thanks that's what i needed
"Chip Pearson" wrote in message ... Olly, First of all, the line of code ThisWorkbook.Saved = True does NOT save the workbook. It tells Excel that the workbook does not need to be saved, but does NOT actually save any changes. Try something like the following: Sub CloseSpreedsheet() Dim Res As Long Res = MsgBox("Do you want to save?", vbYesNoCancel) Select Case Res Case vbYes ThisWorkbook.Save Case vbNo ThisWorkbook.Saved = True Case vbCancel Exit Sub End Select Application.Quit End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com |
All times are GMT +1. The time now is 07:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com