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?

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?

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


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=569937

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!)

"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?

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?

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("A1:p60")
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


Isito wrote:

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?




--

Dave Peterson

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

"Dave Peterson" wrote:

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("A1:p60")
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


Isito wrote:

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?




--

Dave Peterson

"Wuddus" wrote:

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?

"mr_teacher" wrote:


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


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=569937

"mr_teacher" wrote:


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


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=569937

I am a rookie when it comes to macros. I have a similar issue I am trying to figure out. I was wondering if that upon closing the workbook can I have information from one cell transfer to another? Also, when closing, have the information from selected cells be deleted? Thanks in advance for any help.