Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I stop the close method in an auto close macro | Excel Programming | |||
Don't let user close. Use macro button to close? | Excel Programming | |||
Auto Excel workbook close: save= false during an auto subroutine | Excel Programming | |||
Excel shoud not close all active books when clicking close button | Excel Discussion (Misc queries) | |||
excel - Windows close button (x) should only close active workboo. | Setting up and Configuration of Excel |