can I start a macro when the close button is pressed?

[jsambrook]

I want to get the user to enter data into a version control before closing the file. Can i get a macro to trigger as the file is closed?

thanks

 

[tweedle]

Sure.

ALT F11 to open the code editor
On the left tree, go to ThisWorkbook; double click to open the code pane

At the top of the code pane, with the left drop down select Workbook
With the right drop down select BeforeClose

Private Sub Workbook_BeforeClose
 
End Sub

Anything you put in there will run when the workbook closes.

 

[jsambrook]

Genius! thanks so much for that. Didn't realise it was that simple!

 

[jsambrook]

Although I'm sure it will be easy once I find this drop down on the left? I've got the code open but cant find a left drop down? Can you point me a bit closer pls?
Many many thanks

 

[tweedle]

Mr. Pearson provides a graphic of the interface here:
http://www.cpearson.com/excel/Events.aspx

 

[jsambrook]

Thanks Tweedle. I'm still a bit confused as to where I should put this code? I cant seem to get it to trigger when I close.
thanks

 

[jsambrook]

I've managed to get the macro to work, apart from I cant update the version loge as it closes the file as soon as the message box it slicked. How do I cancel the close?


Private Sub Workbook_BeforeClose(cancel As Boolean)

x = MsgBox("Have you update the Version Log", vbYesNo)
If x = 6 Then Exit Sub
Sheets("Version Log").Select
Range("B5").Select
MsgBox ("Update Version Log")
End Sub

 

[gsbelbin]

To stop the workbook closing set cancel to true

Gordon

 

[jsambrook]

thanks gordon