Closing a file and unloading a userform

[birdieman]

I have read about my question on this forum but cannot find the thread.

I want to unload a userform before closing the file and quitting Excel. I was told by smart people on this forum not to just "quit" without unloading the form because doing so leaves stuff (?) in memory.

However, when the form is unloaded, the rest of the code below the unload statement is not run because the code is in the userform. Here is my code.

Private Sub CommandButton5_Click()

ThisWorkbook.Save
Unload UserForm1
ThisWorkbook.Close
MsgBox ("Your data has been saved."), vbInformation
Application.Quit

End Sub

How does one get around this issue?

thanks for looking

 

[Kenneth Hobson]

  ThisWorkbook.Save
  MsgBox ("Your data has been saved."), vbInformation
  Unload UserForm1
  'ThisWorkbook.Close
  Application.Quit

 

[Sektor]

You could use ThisWorkbook_BeforeClose event. In ThisWorkbook's module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Unload UserForm1
    Application.Quit
End Sub

And your original code:

Private Sub CommandButton5_Click()
    ThisWorkbook.Save
    MsgBox ("Your data has been saved."), vbInformation
    ThisWorkbook.Close
End Sub

But I really do not understand why would you go such a hard and unnecessary way.

 

[birdieman]

Ken -- Thanks for your response.

Sektor -- What do you mean by "why would you go such a hard and unnecessary way"? Or, What Should I be doing?

thanks