Auto Close Yes no button

[Simon - M&M]

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

 

[JLatham]

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]

That has worked a treat.

Thanks Very Much

Simon

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.

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

 

[JLatham]

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

That has worked a treat.

Thanks Very Much

Simon

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.

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