How to Capture an Excel Close command

[simdan42]

I need to check the value of some cells in excel before the workbook is closed. If it meets the criteria it saves and closes, if not I need to escape out of the close command and display error messages. Please help

 

[PHV]

You can play with the BeforeClose event of your workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Setting Cancel to True will abort the close command.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

[tt]
BeforeClose Event


Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.

Syntax

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Cancel False when the event occurs. If the event procedure sets this argument to True, the close operation stops and the workbook is left open.
[/tt]

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mattdrinks]

To get your code to run when the workbook is closed place your code in the Workbook_BeforeClose Event.
Something like this should do:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cells(1,1) ="Hello World" Then
    Me.Save
Else
    Cancel = True
    MsgBox "Please Don't Close This File"
End If
End Sub

Setting Cancel to True stops the workbook from closing.
I haven't checked the code yet, but any problems let me know.

Matt

 

[simdan42]

Great guys! Thanks!!