Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Auto Close Yes no button

Hi,

I'm trying to create a Yes No button that activates when the user tries to
close excel, what i want is that if the user selects yes for the workbook to
save and close. If they click No I want them to be able to go back to the
spreadsheet and carry on working. At the moment all i have got is the yes
bit, when they click no the "Do you want to save changes" box appears and i
can't for the life of me get the macro to select cancel.
Here is the code i'm working on at the minute.
Sub Auto_Close()
'
' Auto_Close Macro
' Macro recorded 09/11/2007 by sparrett
'

Application.DisplayAlerts = False
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have You Saved A copy?"
Style = vbYesNo + vbInformation
Title = "Clear Down Button"
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action


Else ' User chose No.
MyString = "No" ' Perform some action.

Application.DisplayAlerts = False
ThisWorkbook.Saved = False


End If
End Sub

Any help would be appreciated.

Cheers

Simon
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,365
Default Auto Close Yes no button

Instead of using Sub Auto_Close() use the workbook's BeforeClose event. It
has a specific Cancel parameter which you can set to TRUE to prevent closing.

To get to the Workbook's code module, right click on the Excel icon
immediately to the left of 'File' in the menu toolbar and choose [View Code]
from the popup list. Choose Workbook from the list at the top (starts off
with General in it) and then choose BeforeClose from the list to the right of
that list.

When you select Workbook from the first list, it will put a stub in for
SelectionChange; you can delete that stub code at any time. When you choose
BeforeClose in the 2nd list, it will provide the stub for that event. You
can put your code within that stub. What you want to do is put a line of
code in the "NO" response part of your msgbox evaluation like this:
Cancel = True
(assumes the question is 'Have you saved a copy?') and you want to remain in
the workbook if a copy hasn't been saved.

When Cancel is set to True, the pending action (Close in this case) is
cancelled.

"Simon - M&M" wrote:

Hi,

I'm trying to create a Yes No button that activates when the user tries to
close excel, what i want is that if the user selects yes for the workbook to
save and close. If they click No I want them to be able to go back to the
spreadsheet and carry on working. At the moment all i have got is the yes
bit, when they click no the "Do you want to save changes" box appears and i
can't for the life of me get the macro to select cancel.
Here is the code i'm working on at the minute.
Sub Auto_Close()
'
' Auto_Close Macro
' Macro recorded 09/11/2007 by sparrett
'

Application.DisplayAlerts = False
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have You Saved A copy?"
Style = vbYesNo + vbInformation
Title = "Clear Down Button"
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action


Else ' User chose No.
MyString = "No" ' Perform some action.

Application.DisplayAlerts = False
ThisWorkbook.Saved = False


End If
End Sub

Any help would be appreciated.

Cheers

Simon

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Auto Close Yes no button

That has worked a treat.

Thanks Very Much

Simon

"JLatham" wrote:

Instead of using Sub Auto_Close() use the workbook's BeforeClose event. It
has a specific Cancel parameter which you can set to TRUE to prevent closing.

To get to the Workbook's code module, right click on the Excel icon
immediately to the left of 'File' in the menu toolbar and choose [View Code]
from the popup list. Choose Workbook from the list at the top (starts off
with General in it) and then choose BeforeClose from the list to the right of
that list.

When you select Workbook from the first list, it will put a stub in for
SelectionChange; you can delete that stub code at any time. When you choose
BeforeClose in the 2nd list, it will provide the stub for that event. You
can put your code within that stub. What you want to do is put a line of
code in the "NO" response part of your msgbox evaluation like this:
Cancel = True
(assumes the question is 'Have you saved a copy?') and you want to remain in
the workbook if a copy hasn't been saved.

When Cancel is set to True, the pending action (Close in this case) is
cancelled.

"Simon - M&M" wrote:

Hi,

I'm trying to create a Yes No button that activates when the user tries to
close excel, what i want is that if the user selects yes for the workbook to
save and close. If they click No I want them to be able to go back to the
spreadsheet and carry on working. At the moment all i have got is the yes
bit, when they click no the "Do you want to save changes" box appears and i
can't for the life of me get the macro to select cancel.
Here is the code i'm working on at the minute.
Sub Auto_Close()
'
' Auto_Close Macro
' Macro recorded 09/11/2007 by sparrett
'

Application.DisplayAlerts = False
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have You Saved A copy?"
Style = vbYesNo + vbInformation
Title = "Clear Down Button"
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action


Else ' User chose No.
MyString = "No" ' Perform some action.

Application.DisplayAlerts = False
ThisWorkbook.Saved = False


End If
End Sub

Any help would be appreciated.

Cheers

Simon

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,365
Default Auto Close Yes no button

Glad I could be of assistance, and thanks for the feedback.

"Simon - M&M" wrote:

That has worked a treat.

Thanks Very Much

Simon

"JLatham" wrote:

Instead of using Sub Auto_Close() use the workbook's BeforeClose event. It
has a specific Cancel parameter which you can set to TRUE to prevent closing.

To get to the Workbook's code module, right click on the Excel icon
immediately to the left of 'File' in the menu toolbar and choose [View Code]
from the popup list. Choose Workbook from the list at the top (starts off
with General in it) and then choose BeforeClose from the list to the right of
that list.

When you select Workbook from the first list, it will put a stub in for
SelectionChange; you can delete that stub code at any time. When you choose
BeforeClose in the 2nd list, it will provide the stub for that event. You
can put your code within that stub. What you want to do is put a line of
code in the "NO" response part of your msgbox evaluation like this:
Cancel = True
(assumes the question is 'Have you saved a copy?') and you want to remain in
the workbook if a copy hasn't been saved.

When Cancel is set to True, the pending action (Close in this case) is
cancelled.

"Simon - M&M" wrote:

Hi,

I'm trying to create a Yes No button that activates when the user tries to
close excel, what i want is that if the user selects yes for the workbook to
save and close. If they click No I want them to be able to go back to the
spreadsheet and carry on working. At the moment all i have got is the yes
bit, when they click no the "Do you want to save changes" box appears and i
can't for the life of me get the macro to select cancel.
Here is the code i'm working on at the minute.
Sub Auto_Close()
'
' Auto_Close Macro
' Macro recorded 09/11/2007 by sparrett
'

Application.DisplayAlerts = False
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have You Saved A copy?"
Style = vbYesNo + vbInformation
Title = "Clear Down Button"
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action


Else ' User chose No.
MyString = "No" ' Perform some action.

Application.DisplayAlerts = False
ThisWorkbook.Saved = False


End If
End Sub

Any help would be appreciated.

Cheers

Simon

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I stop the close method in an auto close macro Paul Excel Programming 2 November 17th 06 02:48 PM
Don't let user close. Use macro button to close? mike Excel Programming 2 October 30th 06 01:31 PM
Auto Excel workbook close: save= false during an auto subroutine tomwashere2 Excel Programming 10 June 16th 05 06:23 AM
Excel shoud not close all active books when clicking close button technomike Excel Discussion (Misc queries) 0 June 10th 05 05:35 PM
excel - Windows close button (x) should only close active workboo. CoffeeAdict Setting up and Configuration of Excel 3 February 8th 05 04:30 AM


All times are GMT +1. The time now is 11:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"