Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Capture an Excel Close command

Status
Not open for further replies.

simdan42

Programmer
Jul 17, 2002
118
US
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
 
You can play with the BeforeClose event of your workbook:
Code:
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
 
[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,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
To get your code to run when the workbook is closed place your code in the Workbook_BeforeClose Event.
Something like this should do:

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top