MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Run on close


Posted by Christopher on April 24, 2001 7:11 AM

I need to execute a userform when they decide to close the workbook, how can I do that?


Posted by Jerid on April 24, 2001 8:25 AM

Hi Christopher

One way would be to do something like this.

'Module level variable
Public bClose As Boolean

'Procedure in the "ThisWorkbook" module
Public Sub Workbook_BeforeClose(Cancel As Boolean)
If bClose = False Then
Cancel = True
UserForm1.Show
Else
Cancel = False
End If
End Sub

'Procedure in UserForm to finally close
Private Sub CommandButton1_Click()
bClose = True
Application.Quit
End Sub

Or you could do something similar in the Auto_Close procedure.

Posted by steve on April 24, 2001 8:28 AM

Right click on the excel icon to the left of file and chose view code and paste in the following

Private Sub Workbook_BeforeClose()
UserForm1.Show
End Sub

steve