Closing a file and unloading a userform

birdieman

Well-known Member
Joined
Jan 13, 2016
Messages
551
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.
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,181
Office Version
  1. 365
Platform
  1. Windows
Code:
  ThisWorkbook.Save
  MsgBox ("Your data has been saved."), vbInformation
  Unload UserForm1
  'ThisWorkbook.Close
  Application.Quit
 
Upvote 0

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,874
Office Version
  1. 365
Platform
  1. Windows
You could use ThisWorkbook_BeforeClose event. In ThisWorkbook's module:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Unload UserForm1
    Application.Quit
End Sub

And your original code:
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.
 
Last edited:
Upvote 0

birdieman

Well-known Member
Joined
Jan 13, 2016
Messages
551
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
 
Upvote 0

Forum statistics

Threads
1,191,719
Messages
5,988,296
Members
440,148
Latest member
sandy123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top