PROPER/CORRECT way to save, close workbook and close Excel through VBA

[birdieman]

I have the following code that I want to save data, save workbook, close workbook and quit the excel application. I think it is closing the workbook before it can execute the Application.Quit line. It is executed by a button.

Private Sub CommandButton4_Click()
Call SaveTextBoxData
ThisWorkbook.Save
MsgBox ("Your data has been saved."), vbInformation
ThisWorkbook.Close
Application.Quit
End Sub

1. What do I change to make it do what I want, i.e., to quit Excel AFTER closing????
2. Is the "ThisWorkbook.save" and "thisworkbook.close" the proper way to save and close (as opposed to "Activeworkbook.save" and "activeworkbook.close"????
3. Does the Application.Quit command automatically and correctly close the workbook???? Do you even need the "Thisworkbook.close" line?

thanks

 

[Scott Huish]

You shouldn't need the Save line:

Private Sub CommandButton4_Click()
Call SaveTextBoxData
MsgBox ("Your data has been saved."), vbInformation
ThisWorkbook.Close (True)
Application.Quit
End Sub

 

[birdieman]

why not? I want to save the workbook (in addition to saving my textbox data to the workbook). What about the other questions numbered 1,2,3?

 

[Scott Huish]

The TRUE parameter of ThisWorkbook.Close tells it to save changes.

You want to save your workbook somehow before quitting the application.

 

[birdieman]

Ok I understand the True statement. But since I have Application.Quit as the last line, doesn't the workbook save AND close BEFORE it gets to the .Quit, thus never executing the .Quit line and leaving Excel open?

 

[GTO]

1. What do I change to make it do what I want, i.e., to quit Excel AFTER closing????
2. Is the "ThisWorkbook.save" and "thisworkbook.close" the proper way to save and close (as opposed to "Activeworkbook.save" and "activeworkbook.close"????
3. Does the Application.Quit command automatically and correctly close the workbook???? Do you even need the "Thisworkbook.close" line?

Ok I understand the True statement. But since I have Application.Quit as the last line, doesn't the workbook save AND close BEFORE it gets to the .Quit, thus never executing the .Quit line and leaving Excel open?

Greetings,

From your questions... You cannot quit the application (or do anything else for that matter) after the code quits running, which it does upon the WB closing. In short, you may want the code to check for other open WBs, see if they are saved or handle if they are not, close them, then after saving ThisWorkbook, quit the application.

Always use ThisWorkbook instead of ActiveWorkbook, unless the code really needs to run against another open workbook (in which case I would set a reference to the other WB and use the object name).

Anyways, here is an example of something like what you might be trying to do.

Hope it helps,

Mark

Option Explicit
'
Private Sub CommandButton4_Click()
Dim WB As Workbook
'
  'Call SaveTextBoxData
'
  If Not ThisWorkbook.Saved Then
    ThisWorkbook.Save
    MsgBox "Your data has been saved.", vbInformation, vbNullString
  End If
'
  For Each WB In Application.Workbooks
    If Not WB.Saved Then
      If MsgBox("You are about to exit the application.  The workbook named """ & WB.Name & """ is not saved.  Would you like to save """ & WB.Name & """ before it closes?", vbQuestion Or vbYesNo Or vbDefaultButton1, "Save Workbook...?") = vbYes Then
        WB.Close SaveChanges:=True
        DoEvents
      Else
        'WB.Saved = True
        WB.Close SaveChanges:=False
      End If
    End If
  Next
'
  'All workbooks (except this one) are closed, and we already saved this workbook, so just quit the application.
  Application.Quit
End Sub

 

[birdieman]

Thanks for responding, but I am still confused. I have one workbook (bunch of sheets). I have two command buttons on a userform.

Command button label " Save and Exit". I want to save the one workbook, close it (if necessary), and then Quit/close Excel. The code:

Private Sub CommandButton4_Click()
Call SaveTextBoxData
ThisWorkbook.Save
MsgBox ("Your data has been saved."), vbInformation
Application.Quit
End Sub
[code]
Does this code do what I want?


The second button label "Exit Without Saving".  I simply want to close the file (if necessary) and quit excel without saving the file.  The code.

[code]
Private Sub CommandButton13_Click()
Application.DisplayAlerts = False
Application.Quit
Application.DisplayAlerts = True
End Sub

I think this piece of code needs some changing ???

 

[GTO]

Thanks for responding, but I am still confused. I have one workbook (bunch of sheets). I have two command buttons on a userform.

Command button label " Save and Exit". I want to save the one workbook, close it (if necessary), and then Quit/close Excel. The code:

Private Sub CommandButton4_Click()
Call SaveTextBoxData
ThisWorkbook.Save
MsgBox ("Your data has been saved."), vbInformation
Application.Quit
End Sub

Does this code do what I want?

Not tested and I have no idea of what happens in SaveTextData, but yes, I think that should work. Not being obstinate, but it does not check to see if the user has other workbooks open, or whether any such other workbook is saved.

The second button label "Exit Without Saving". I simply want to close the file (if necessary) and quit excel without saving the file. The code.

Private Sub CommandButton13_Click()
Application.DisplayAlerts = False
Application.Quit
Application.DisplayAlerts = True
End Sub

I think this piece of code needs some changing ???

Skipping the part about any possible other workbooks, rather than killing alerts, why not just ThisWorkbook.Saved = True, then Application.Quit ?

Hope that helps,

Mark

 

[birdieman]

yes it does. thank you and thanks for the code that checks if other workbooks are open.

By the way, I did what you said just above here regarding ThisWorkbook.Saved=True, then application quit. Upon opening again, excel crashed without giving me a reason. I know you do not know my code, but could this way of closing/quitting have anything to do with it? I could then open the file with macros disabled, and then ran the userform using the "run" command and everything worked fine.

 

[birdieman]

Hey GTO
Was looking at your code (making sure all workbooks are saved). Why is one line commented out (WB.Saved=True)? I am still relatively new to VBA and trying to figure it out