Msgbox not working
 

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!

Msgbox not working
 

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!

Msgbox not working
 

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.

Msgbox not working
 

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.

Msgbox not working
 

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.

Msgbox not working
 

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.

Msgbox not working
 

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

Msgbox not working
 

Dave Peterson wrote:
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

Are those all references to BeforeSave, or to the fact that BeforeSave
must be in ThisWorkbook? In the 2000 manual, there are only 2
references to BeforeSave. One mentions it in a list of workbook events
(p. 524), the other elaborates on the list with 3 paragraphs (p. 526).
Neither mentions that particular requirement. I also read most of the
material on event handling when I encountered the problem. It isn't
mentioned in that section of the book.
I suspect he improved the index in the 2002 version. I have the 2000
version, since I'm working with XL2000, and none of the references you
gave have any mention of BeforeSave, much less that it has to be in
ThisWorkbook. If he improved the index, than hat's off to him. It
needed it.
I imagine that he does mention this requirement somewhere in the 2000
manual. It's just not in any of the indexed entries and therefore
pretty hard to find. I knew when I bought the book it was not well
indexed, it has an index ratio of about 4. But it was indexed as well
as any of the other books, and looked better overall. From having done
some manual writing myself (XL in fact), I know that the indexing is
pretty tedious work, usually not an author's favorite. It often is
neglected.
It's a good manual, when I can find what I'm seeking...

Msgbox not working
 

They were references to workbook_open and other workbook level events.

davegb wrote:

Dave Peterson wrote:
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

Are those all references to BeforeSave, or to the fact that BeforeSave
must be in ThisWorkbook? In the 2000 manual, there are only 2
references to BeforeSave. One mentions it in a list of workbook events
(p. 524), the other elaborates on the list with 3 paragraphs (p. 526).
Neither mentions that particular requirement. I also read most of the
material on event handling when I encountered the problem. It isn't
mentioned in that section of the book.
I suspect he improved the index in the 2002 version. I have the 2000
version, since I'm working with XL2000, and none of the references you
gave have any mention of BeforeSave, much less that it has to be in
ThisWorkbook. If he improved the index, than hat's off to him. It
needed it.
I imagine that he does mention this requirement somewhere in the 2000
manual. It's just not in any of the indexed entries and therefore
pretty hard to find. I knew when I bought the book it was not well
indexed, it has an index ratio of about 4. But it was indexed as well
as any of the other books, and looked better overall. From having done
some manual writing myself (XL in fact), I know that the indexing is
pretty tedious work, usually not an author's favorite. It often is
neglected.
It's a good manual, when I can find what I'm seeking...

--

Dave Peterson

Msgbox not working
 

Dave Peterson wrote:
They were references to workbook_open and other workbook level events.

Ok. I have plenty of those in my book, just not anything on the
requirement that BeforeSave must be in the ThisWorkbook sheet.
Thanks to all for your help!


davegb wrote:

Dave Peterson wrote:
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

Are those all references to BeforeSave, or to the fact that BeforeSave
must be in ThisWorkbook? In the 2000 manual, there are only 2
references to BeforeSave. One mentions it in a list of workbook events
(p. 524), the other elaborates on the list with 3 paragraphs (p. 526).
Neither mentions that particular requirement. I also read most of the
material on event handling when I encountered the problem. It isn't
mentioned in that section of the book.
I suspect he improved the index in the 2002 version. I have the 2000
version, since I'm working with XL2000, and none of the references you
gave have any mention of BeforeSave, much less that it has to be in
ThisWorkbook. If he improved the index, than hat's off to him. It
needed it.
I imagine that he does mention this requirement somewhere in the 2000
manual. It's just not in any of the indexed entries and therefore
pretty hard to find. I knew when I bought the book it was not well
indexed, it has an index ratio of about 4. But it was indexed as well
as any of the other books, and looked better overall. From having done
some manual writing myself (XL in fact), I know that the indexing is
pretty tedious work, usually not an author's favorite. It often is
neglected.
It's a good manual, when I can find what I'm seeking...

--

Dave Peterson

Msgbox not working
 

First any typos or mispellings are my own.
Walkenbach's Microsoft Excel 2000 Power Programming with VBA

It's not in the 3 paragraphs in the detailed
description of BeforeSave on page 526.

But this is in a section starting on 523 Named Workbook-Level Events.(about
2/3rds down the page)

He has an introduction to these events on the remainder of Page 523, then
On 524, he lists all the events in Table 18-1.

Then he goes through and provides some explanation and sample code for each
of the most common events, with

Open Event
Activate Event
SheetActivate Event
NewSheet Event
BeforeSave Event (page 526 as you state)
and then three more.

Back to 523, first paragraph under the Header Workbook-Level Events in the
introduction to these events -- Starts with:

Workbook Level events occur for a particular workbook. Table 18-1 lists the
Workbook events, along with a brief description of each. **Workbook
event-handler procedures are stored in the code module for ThisWorkbook
object**

Then after 2 small paragraphs (about an inch of space) and at the bottom of
the page in BOLD

**All the example procedures that follow must be located in the code module
for the ThisWorkbook object. If you put them into any other type of code
module, they will not work.**

In the Index under

Events
Workbook-Level 518, 523-529

and for the particular event:

BeforeSave Event 524, 526

In the Table of Contents

Chapter 18: Understanding Excel's Events 518
. . .
What you should know about events 518
. . .
Where to put event handler procedures 518
.. . .
Workbook-Level Events 523

----------------

Perhaps restating the location in the description of each of 8 events
described would eliminate any chance of oversight, but I don't see it
inconsistent to emphasize the point twice at the beginning of the section on
the family of workbook level events.

In my opinion, an obvious place to look if one is having trouble. In any
event, It seems wildly misrepresentative to make a statement like

You'd think Walkenbach would've mentioned it had to be
in the "ThisWorkbook" module!

Particularly when you followed up with:

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.

where did you look?


Might help me to figure out
where he puts things if I could find it.

Hope that gives you a sense of where to look.

--
HTH,
Tom Ogilvy



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

Dave Peterson wrote:
They were references to workbook_open and other workbook level events.

Ok. I have plenty of those in my book, just not anything on the
requirement that BeforeSave must be in the ThisWorkbook sheet.
Thanks to all for your help!


davegb wrote:

Dave Peterson wrote:
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

Are those all references to BeforeSave, or to the fact that BeforeSave
must be in ThisWorkbook? In the 2000 manual, there are only 2
references to BeforeSave. One mentions it in a list of workbook events
(p. 524), the other elaborates on the list with 3 paragraphs (p. 526).
Neither mentions that particular requirement. I also read most of the
material on event handling when I encountered the problem. It isn't
mentioned in that section of the book.
I suspect he improved the index in the 2002 version. I have the 2000
version, since I'm working with XL2000, and none of the references you
gave have any mention of BeforeSave, much less that it has to be in
ThisWorkbook. If he improved the index, than hat's off to him. It
needed it.
I imagine that he does mention this requirement somewhere in the 2000
manual. It's just not in any of the indexed entries and therefore
pretty hard to find. I knew when I bought the book it was not well
indexed, it has an index ratio of about 4. But it was indexed as well
as any of the other books, and looked better overall. From having done
some manual writing myself (XL in fact), I know that the indexing is
pretty tedious work, usually not an author's favorite. It often is
neglected.
It's a good manual, when I can find what I'm seeking...

--

Dave Peterson

Msgbox not working
 

Guess I get to eat some crow on this one. No point in making excuses, I
must have been rushed and not looking everywhere I should have. Thanks
for showing me that it's there (twice) and that I need to peruse the
material and make sure I fully understand each line before moving on.
Actually, I'm glad it was there, it restores my faith in Walkenbach. My
own shortcomings I can correct much easier than someone elses.
Thanks for the help!

Msgbox not working
 

Sometimes, it's like checking spelling in a dictionary. If you know how to
spell it, you can find it quickly--but then why bother!



davegb wrote:

Guess I get to eat some crow on this one. No point in making excuses, I
must have been rushed and not looking everywhere I should have. Thanks
for showing me that it's there (twice) and that I need to peruse the
material and make sure I fully understand each line before moving on.
Actually, I'm glad it was there, it restores my faith in Walkenbach. My
own shortcomings I can correct much easier than someone elses.
Thanks for the help!

--

Dave Peterson

Msgbox not working
 

And one more point...

When using events, if you select the object from the object drop down
list at the top of the module and the event procedure from the
procedures drop down rather than just typing it in you will be a lot
less likely to get it wrong.

Regards
Rowan


Dave Peterson wrote:
Sometimes, it's like checking spelling in a dictionary. If you know how to
spell it, you can find it quickly--but then why bother!



davegb wrote:

Guess I get to eat some crow on this one. No point in making excuses, I
must have been rushed and not looking everywhere I should have. Thanks
for showing me that it's there (twice) and that I need to peruse the
material and make sure I fully understand each line before moving on.
Actually, I'm glad it was there, it restores my faith in Walkenbach. My
own shortcomings I can correct much easier than someone elses.
Thanks for the help!