In the Workbook Save event I want to give the user the chance to abort
the save by responding to a YesNo MessageBox.

I've got an integer variable called 'answer' which is set by the
MessageBox, and have tried both the following lines of code, expecting
that they might exit the save event but they don't.

If answer = 7 Then End
If answer = 7 Then Exit Sub

Has anyone any idea how I can handle this.

Regards

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

If answer = 7 Then cancel = True

--
HTH

Bob Phillips

"Richard Buttrey" wrote in
message ...
In the Workbook Save event I want to give the user the chance to abort
the save by responding to a YesNo MessageBox.

I've got an integer variable called 'answer' which is set by the
MessageBox, and have tried both the following lines of code, expecting
that they might exit the save event but they don't.

If answer = 7 Then End
If answer = 7 Then Exit Sub

Has anyone any idea how I can handle this.

Regards

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Fri, 29 Jul 2005 17:47:52 +0100, "Bob Phillips"
wrote:

If answer = 7 Then cancel = True


Ah, Thanks Bob. So simple.

The other mistake I made was to code this in a Procedure which was
called from the Workbook Save event - and clearly VBA thought the word
cancel was a variable.

Just as a matter of interest, I tend to call Procedures from
Workbook/Worksheet events rather than code them in the event itself.

I've tended to do this since it seems to make testing (with the F8
key) slightly easier.

Any thoughts on this. Is it bad practice and are there any particular
implications?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

I wondered about that, but though, nah, he knows :-).

I would hesitate to call it good practice or bad practice myself Richard, as
there are various factors which could change your choice. Personally, whilst
I know what you mean about F8, it only really relates to events with
arguments, you can just step into the others. Those with arguments are a bit
harder, but rarely insurmountable.

As a starting rule of thumb, I tend to try and keep all of the code that
arise from an event in the event module. However, if I am dealing with many
events, and/or the action is complex, I tend to strip most of the work out,
and put them in a standard code module that is purely for event related
code. I do this so that I can easily digest the main purpose of my event
module at a glance. Also, if I have some code that might be used in more
than one event module, rather than replicate it, I would create a function
in a standard module for that.

--
HTH

Bob Phillips

"Richard Buttrey" wrote in
message ...
On Fri, 29 Jul 2005 17:47:52 +0100, "Bob Phillips"
wrote:

If answer = 7 Then cancel = True


Ah, Thanks Bob. So simple.

The other mistake I made was to code this in a Procedure which was
called from the Workbook Save event - and clearly VBA thought the word
cancel was a variable.

Just as a matter of interest, I tend to call Procedures from
Workbook/Worksheet events rather than code them in the event itself.

I've tended to do this since it seems to make testing (with the F8
key) slightly easier.

Any thoughts on this. Is it bad practice and are there any particular
implications?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________