Hi,

I am new to online help and I hope that somebody will be able to help me.

I have a similar problem, I want to run a macro when a user tries to close a
workbook and I used different approach - Sub Auto_Close()
I am able to run a macro but the problem is that I cannot cancel the
closing. The idea of the macro is to check if there are any highlighted cells
(meaning that not all data is entered correctly) and offer a user to either
close the book or cancel closing and continue editing. The macro works,
displays the correct message when there are mistakes in the worksheet however
it closes no matter what.

I know that this private sub would solve the problem:

Private Sub WorkbookBeforeClose(Cancel As Boolean)
a = MsgBox("Do you really want to close the workbook?", vbYesNo)
If a = vbNo Then Cancel = True
End Sub

however I dont know how to run private subs (sorry if it is too dumb). I
usually let excel record the macro and edit the program afterwords.

Could anyone please either help me edit my macro (see below) or explain how
to run the private sub above?

Sub Auto_Close()

Sheets("Test").Select
For i = 1 To 60
For k = 1 To 16
If Cells(i, k).Interior.ColorIndex = 35 Or Cells(i,
k).Interior.ColorIndex = 3 Then GoTo CClose
Next
Next
GoTo Endok
CClose: a = MsgBox("Do you really want to close the workbook?", vbYesNo)
If a = vbNo Then ???? DONT CLOSE
Endok: End Sub


Thank you very much.

I.S.
"Paul B" wrote:

Wuddus,

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'your code
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Wuddus" wrote in message
...
I have a macro that ideally would run automatically whenever the user
closes
out of the workbook. I know how to set a macro to run this way when
OPENING
the workbook, but is the reverse possible?