Control or disable "Do you want to save changes" msg

[MatthewGB]

Hi,

Is there any way to either control or disable the above message which appears you close a workbook?

I have tried using the displayalerts and enableevents method in the Before_Close workbook event, but I can't seem to get it working as I would like.

Basically, I want the user to be able to click either my custom Exit button, or the Excel close button in the top right corner of the window and application to close down the program.

Directly after the user clicks, I have code which restores a number of command bars etc. This is fine, except when the user hits the cancel button on the Excel Save Changes box. It continues to run the code which restores the command bars, but does not shut down the program.

Am I able to detect whether the cancel button has been clicked, and then maybe tell the program to skip the restore code?

Thanks

 

[RobBroekhuis]

What do you want the standard response to be? Yes - save changes, or no- do not save? You can program it either way, so you won't get the alert. If you want to leave your users the choice it's more difficult - better to program the choice into your own event handler, rather than relying on Excel's standard action.
Rob

 

[combo]

What you need is an add-in, wchich will perform some tasks:
1. Add menu item when installed,
2. Manage it,
3. Trap saving event and do something depending on setting.

The easiest one, can be something like:
[tt]Sub AddOption()
Dim mItm As CommandBarButton
Dim bConfirmSave As Boolean
bConfirmSave = GetSetting(appname:="DoConfirm", section:="X", Key:="Confirm", Default:=False)
Set mItm = Application.CommandBars("Worksheet Menu Bar&quot.Controls(1).Controls.Add(Type:=msoControlButton, Before:=6, temporary:=True)
With mItm
.Caption = "Confirm saving changes"
If bConfirmSave Then
.State = msoButtonDown
Else
.State = msoButtonUp
End If
.OnAction = "SetSavingOptions"
End With
End Sub[/tt]

The macro SetSavingOptions should only change button state (up/down) and change registry settings, via SaveSetting appname:= etc.

When opening this add-in you should activate application events, and write BeforeSave event basing on the registry setting of the menuitem. (for trapping application events see excel help or thread707-499095).

combo

 

[andrew299]

put this code in the this workkbook bit of your project

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close savechanges = False

End Sub

If I have another workbook open and close one by name
- workbook("xxx&quot.close . ...... -
it closes without saving
but the code above saves even though it should not but does remove the message box

Has anyone elese experienced this anomoly?

andrew299 It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[RobBroekhuis]

Combo's suggestions are excellent for the case where you need to manage multiple workbooks in a uniform way. Matt's original question appeared to refer only to a single workbook. In that case, it should be possible to handle all this within the workbooks' code, without the need for an AddIn or application events. So the best answer, as always, depends on which question is being asked ;-)
Rob