I wrote the following to remind users to enter dates when they save the
worksheet/book:

Private Sub Workbook_BeforeSave()

MsgBox "Did you enter the dates?"

End Sub

But the message box doesn't show. The macro is in the sheet I'm saving.

Any ideas?
Thanks!

When you use an event it must match the event signature specified by VBA
and be in the correct code sheet. The BeforeSave event must be in the
ThisWorkbook module and should look something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim ans As Long
ans = MsgBox("Did you enter the dates?", vbYesNo)
If ans = vbNo Then
Cancel = True
MsgBox "File not saved"
End If
End Sub

Hope this helps
Rowan

davegb wrote:
I wrote the following to remind users to enter dates when they save the
worksheet/book:

Private Sub Workbook_BeforeSave()

MsgBox "Did you enter the dates?"

End Sub

But the message box doesn't show. The macro is in the sheet I'm saving.

Any ideas?
Thanks!

Thanks, Rowan. You'd think Walkenbach would've mentioned it had to be
in the "ThisWorkbook" module!
BTW, it works just fine with just the message and defaulting to the
vbOkonly style.

I'm sure he does.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
Thanks, Rowan. You'd think Walkenbach would've mentioned it had to be
in the "ThisWorkbook" module!
BTW, it works just fine with just the message and defaulting to the
vbOkonly style.

Tom Ogilvy wrote:
I'm sure he does.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
Thanks, Rowan. You'd think Walkenbach would've mentioned it had to be
in the "ThisWorkbook" module!
BTW, it works just fine with just the message and defaulting to the
vbOkonly style.


Any idea where it is? I looked through most of the referenced pages
from the index under "events" and "BeforeSave" before I posted my
original message. It's not in the 3 paragraphs in the detailed
description of BeforeSave on page 526. Might help me to figure out
where he puts things if I could find it.

John has written many books. So I can't say which you are talking about.
But if it isn't actually in that section then it would be in a general into
to events or perhaps in an intro to workbook level events.

It could as a simple and as subtle as

"These two event-handler procedures (which must be in the code module for
the ThisWorkbook object) are listed below."

which is on his site at

http://www.j-walk.com/ss/excel/tips/tip78.htm

A list of books at:

http://www.j-walk.com/ss/books/index.htm


"davegb" wrote in message
ups.com...

Tom Ogilvy wrote:
I'm sure he does.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
Thanks, Rowan. You'd think Walkenbach would've mentioned it had to be
in the "ThisWorkbook" module!
BTW, it works just fine with just the message and defaulting to the
vbOkonly style.


Any idea where it is? I looked through most of the referenced pages
from the index under "events" and "BeforeSave" before I posted my
original message. It's not in the 3 paragraphs in the detailed
description of BeforeSave on page 526. Might help me to figure out
where he puts things if I could find it.

Excel 2002 Power Programming with VBA.

Page 233, 415, 500, 574, 575, 576, ... then I got tired.


davegb wrote:

Tom Ogilvy wrote:
I'm sure he does.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
Thanks, Rowan. You'd think Walkenbach would've mentioned it had to be
in the "ThisWorkbook" module!
BTW, it works just fine with just the message and defaulting to the
vbOkonly style.


Any idea where it is? I looked through most of the referenced pages
from the index under "events" and "BeforeSave" before I posted my
original message. It's not in the 3 paragraphs in the detailed
description of BeforeSave on page 526. Might help me to figure out
where he puts things if I could find it.

--

Dave Peterson