Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Macro when close spreadsheet | Excel Discussion (Misc queries) | |||
macro to close excel | Excel Discussion (Misc queries) | |||
How to close a workbook which contains the macro that's just been run? | Excel Programming | |||
Macro Close 2 | Excel Programming | |||
Close Macro | Excel Programming |