Set macro to run automatically when closing workbook?

[Guest]

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?

 

[Paul B]

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

 

[mr_teacher]

Yes it's possible,

Go to the Visual Basic Editor, select This Workbook by double
clicking.

In the box that appears on the left hand side select Workbook and on
the right hand side select BeforeClose

Then put your macro code inbetween this and it will run before you
close the worksheet.

Hope this does what you're after
Regards

Carl

 

[Guest]

Thanks! I didn't know about that one at all. (And thanks for the fast reply,
too: Maybe I can get this thing done this morning yet!)

 

[Guest]

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.

 

[Dave Peterson]

First, the workbook_beforeclose event isn't something you call--it's an event
that excel is always listening for (unless you tell it not to listen!).

Put the code in the ThisWorkbook module, not a General module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

And don't change the name of the procedure. Excel knows what names to use. If
you change it, excel won't find it.

I don't like to branch using goto's. I think that this works the way you want.

Compiled, but not tested:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim OkToClose As Boolean
Dim myRngToCheck As Range
Dim myCell As Range
Dim Resp As Long

OkToClose = True
With Worksheets("Test")
Set myRngToCheck = .Range("A160")
End With
For Each myCell In myRngToCheck.Cells
If myCell.Interior.ColorIndex = 35 _
Or myCell.Interior.ColorIndex = 3 Then
OkToClose = False
'stop looking for more problems
Exit For
End If
Next myCell

If OkToClose Then
'don't ask the question, and just close
Else
Resp = MsgBox("Do you really want to close the workbook?", vbYesNo)
If Resp = vbNo Then
Cancel = True
End If
End If

End Sub

 

[Guest]

Thank you very much - I was able to fix it, you helped a lot!!!

 

[syed]

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?

 

[syed]

Yes it's possible,

Go to the Visual Basic Editor, select This Workbook by double
clicking.

In the box that appears on the left hand side select Workbook and on
the right hand side select BeforeClose

Then put your macro code inbetween this and it will run before you
close the worksheet.

Hope this does what you're after
Regards

Carl

 

[syed]

Yes it's possible,

Go to the Visual Basic Editor, select This Workbook by double
clicking.

In the box that appears on the left hand side select Workbook and on
the right hand side select BeforeClose

Then put your macro code inbetween this and it will run before you
close the worksheet.

Hope this does what you're after
Regards

Carl