On Close macro
 

Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in message
...
Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

To be reallllly pick about Bob's code ans should be declared as an integer.
Without specifying as Bob has done you end up with a variant which requires
extra overhead. In this application it won't really make any difference but
it is alway good programming to declare your variable explicitly. This is a
really picky point but I get people asking me why when they type the dot
after a key word "why they don't get a drop down list". Very often this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in message
...
Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

Hi Jim,

Although not the case in this specific instance, I declare ans as Variant to
cater for Cancel on Inputbox etc. What I should have used is

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Are you sure you want tos save this file", _
vbYesNo) = vbNo Then
Cancel = True
End If
End Sub

No variable :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
To be reallllly pick about Bob's code ans should be declared as an
integer.
Without specifying as Bob has done you end up with a variant which
requires
extra overhead. In this application it won't really make any difference
but
it is alway good programming to declare your variable explicitly. This is
a
really picky point but I get people asking me why when they type the dot
after a key word "why they don't get a drop down list". Very often this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in message
...
Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

Thankyou both for your replies! That has worked perfectly.

Could I paste another the same but with
Workbook_BeforeSave. I've tried to copy the whole thing
and paste it again in the code window, change Close to
Save and it doesn't work. Is there such a thing as
BeforeSave?

Thank you again

Judith

-----Original Message-----
Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this
file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on
the worksheet
'(or next to the File menu if you maximise your
workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee"
wrote in message
...
Hello there,

I have spreadsheets that are updated by various
people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click
Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith


.

On Close macro
 

Hey Jim,

I can be even pickier :-). Ans should be declared as Long not Integer.

Sorry, just a laugh, couldn't resist.

Regards

Bob

"Jim Thomlinson" wrote in message
...
To be reallllly pick about Bob's code ans should be declared as an
integer.
Without specifying as Bob has done you end up with a variant which
requires
extra overhead. In this application it won't really make any difference
but
it is alway good programming to declare your variable explicitly. This is
a
really picky point but I get people asking me why when they type the dot
after a key word "why they don't get a drop down list". Very often this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in message
...
Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

Hi Judith,

There is a BeforeSave, and sorry that is what I should have offered first
time. It has different arguments

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If MsgBox("Are you sure you want tos save this file", _
vbYesNo) = vbNo Then
Cancel = True
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in message
...
Thankyou both for your replies! That has worked perfectly.

Could I paste another the same but with
Workbook_BeforeSave. I've tried to copy the whole thing
and paste it again in the code window, change Close to
Save and it doesn't work. Is there such a thing as
BeforeSave?

Thank you again

Judith

-----Original Message-----
Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this
file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on
the worksheet
'(or next to the File menu if you maximise your
workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee"
wrote in message
...
Hello there,

I have spreadsheets that are updated by various
people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click
Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith


.

On Close macro
 

For what it's worth, the data type of the return value from a
Msgbox is a "Long".

Jim Cone
San Francisco, USA


"Jim Thomlinson" wrote in message
...
To be reallllly pick about Bob's code ans should be declared as an integer.
Without specifying as Bob has done you end up with a variant which requires
extra overhead. In this application it won't really make any difference but
it is alway good programming to declare your variable explicitly. This is a
really picky point but I get people asking me why when they type the dot
after a key word "why they don't get a drop down list". Very often this is
the reason....
dim ans as integer
HTH

On Close macro
 

Thank you all for your help. And ever confusing exchange
lol :)

I have copied and pasted the BeforeSave VB and it is
showing red in the code window?

I'll calling on your expertise again!

Judith

-----Original Message-----
Hey Jim,

I can be even pickier :-). Ans should be declared as
Long not Integer.

Sorry, just a laugh, couldn't resist.

Regards

Bob

"Jim Thomlinson"
wrote in message
news:661349D4-D960-4EF9-A51D-
...
To be reallllly pick about Bob's code ans should be
declared as an
integer.
Without specifying as Bob has done you end up with a
variant which
requires
extra overhead. In this application it won't really
make any difference
but
it is alway good programming to declare your variable
explicitly. This is
a
really picky point but I get people asking me why when
they type the dot
after a key word "why they don't get a drop down
list". Very often this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save
this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon
on the worksheet
'(or next to the File menu if you maximise your
workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing
direct)


"JudithJubilee"
wrote in message
...
Hello there,

I have spreadsheets that are updated by various
people. I
would like a macro that puts up a message box when
they
save or close to ask if they are sure. If they
click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith





.

On Close macro
 

Hi Judith,

It's the damn NG wrap. The Boolean ) on a line all of its own should be part
of the first line.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in message
...
Thank you all for your help. And ever confusing exchange
lol :)

I have copied and pasted the BeforeSave VB and it is
showing red in the code window?

I'll calling on your expertise again!

Judith

-----Original Message-----
Hey Jim,

I can be even pickier :-). Ans should be declared as
Long not Integer.

Sorry, just a laugh, couldn't resist.

Regards

Bob

"Jim Thomlinson"
wrote in message
news:661349D4-D960-4EF9-A51D-
...
To be reallllly pick about Bob's code ans should be
declared as an
integer.
Without specifying as Bob has done you end up with a
variant which
requires
extra overhead. In this application it won't really
make any difference
but
it is alway good programming to declare your variable
explicitly. This is
a
really picky point but I get people asking me why when
they type the dot
after a key word "why they don't get a drop down
list". Very often this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save
this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon
on the worksheet
'(or next to the File menu if you maximise your
workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing
direct)


"JudithJubilee"
wrote in message
...
Hello there,

I have spreadsheets that are updated by various
people. I
would like a macro that puts up a message box when
they
save or close to ask if they are sure. If they
click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith





.

On Close macro
 

True but a message box can not return a long... At least not that I have ever
run across.... I'll stick with my integers... That being said your second
code is my favorite implementation... Avoid the overhead of a variable... Now
that is picky...

"Bob Phillips" wrote:

Hey Jim,

I can be even pickier :-). Ans should be declared as Long not Integer.

Sorry, just a laugh, couldn't resist.

Regards

Bob

"Jim Thomlinson" wrote in message
...
To be reallllly pick about Bob's code ans should be declared as an
integer.
Without specifying as Bob has done you end up with a variant which
requires
extra overhead. In this application it won't really make any difference
but
it is alway good programming to declare your variable explicitly. This is
a
really picky point but I get people asking me why when they type the dot
after a key word "why they don't get a drop down list". Very often this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in message
...
Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

You are all absolute stars!!!

Thank you so much.

Judith

-----Original Message-----
Hi Judith,

It's the damn NG wrap. The Boolean ) on a line all of
its own should be part
of the first line.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee"
wrote in message
...
Thank you all for your help. And ever confusing
exchange
lol :)

I have copied and pasted the BeforeSave VB and it is
showing red in the code window?

I'll calling on your expertise again!

Judith

-----Original Message-----
Hey Jim,

I can be even pickier :-). Ans should be declared as
Long not Integer.

Sorry, just a laugh, couldn't resist.

Regards

Bob

"Jim Thomlinson"
wrote in
message
news:661349D4-D960-4EF9-A51D-
...
To be reallllly pick about Bob's code ans should be
declared as an
integer.
Without specifying as Bob has done you end up with a
variant which
requires
extra overhead. In this application it won't really
make any difference
but
it is alway good programming to declare your
variable
explicitly. This is
a
really picky point but I get people asking me why
when
they type the dot
after a key word "why they don't get a drop down
list". Very often this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As
Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save
this file", vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon
on the worksheet
'(or next to the File menu if you maximise your
workbooks),
'select View Code from the menu, and paste the
code


--

HTH

RP
(remove nothere from the email address if mailing
direct)


"JudithJubilee"
wrote in message
...
Hello there,

I have spreadsheets that are updated by various
people. I
would like a macro that puts up a message box
when
they
save or close to ask if they are sure. If they
click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith





.



.

On Close macro
 

VbMsgBoxSetForeground 65536 Specifies the message box window as the
foreground window
vbMsgBoxRight 524288 Text is right aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as
right-to-left reading on Hebrew and Arabic systems


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
True but a message box can not return a long... At least not that I have
ever
run across.... I'll stick with my integers... That being said your second
code is my favorite implementation... Avoid the overhead of a variable...
Now
that is picky...

"Bob Phillips" wrote:

Hey Jim,

I can be even pickier :-). Ans should be declared as Long not Integer.

Sorry, just a laugh, couldn't resist.

Regards

Bob

"Jim Thomlinson" wrote in
message
...
To be reallllly pick about Bob's code ans should be declared as an
integer.
Without specifying as Bob has done you end up with a variant which
requires
extra overhead. In this application it won't really make any
difference
but
it is alway good programming to declare your variable explicitly. This
is
a
really picky point but I get people asking me why when they type the
dot
after a key word "why they don't get a drop down list". Very often
this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this file",
vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in
message
...
Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

Now I am impressed... I stand corrected. I will use long from now on... I
still stand by not using a variant though unless you need a variant... If you
are ever in town I owe you dinner.

:) And once again I learn something new...

"Bob Phillips" wrote:

VbMsgBoxSetForeground 65536 Specifies the message box window as the
foreground window
vbMsgBoxRight 524288 Text is right aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as
right-to-left reading on Hebrew and Arabic systems


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
True but a message box can not return a long... At least not that I have
ever
run across.... I'll stick with my integers... That being said your second
code is my favorite implementation... Avoid the overhead of a variable...
Now
that is picky...

"Bob Phillips" wrote:

Hey Jim,

I can be even pickier :-). Ans should be declared as Long not Integer.

Sorry, just a laugh, couldn't resist.

Regards

Bob

"Jim Thomlinson" wrote in
message
...
To be reallllly pick about Bob's code ans should be declared as an
integer.
Without specifying as Bob has done you end up with a variant which
requires
extra overhead. In this application it won't really make any
difference
but
it is alway good programming to declare your variable explicitly. This
is
a
really picky point but I get people asking me why when they type the
dot
after a key word "why they don't get a drop down list". Very often
this is
the reason....

dim ans as integer

HTH

"Bob Phillips" wrote:

Hi Judith,

Here is some code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans
ans = MsgBox("Are you sure you want tos save this file",
vbYesNo)
If ans = vbNo Then
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JudithJubilee" wrote in
message
...
Hello there,

I have spreadsheets that are updated by various people. I
would like a macro that puts up a message box when they
save or close to ask if they are sure. If they click Yes
then continue. If No then cancels the action.

I'm a beginner in VB but willing to try it!

Judith

On Close macro
 

"Jim Thomlinson" wrote in message
...
Now I am impressed... I stand corrected. I will use long from now on... I
still stand by not using a variant though unless you need a variant...

No argument with you there Jim, that was laziness on my part in that case.

If you are ever in town I owe you dinner.

Where is Jin's town for filing away :-).

:) And once again I learn something new...

The glory of these NGs.