Can I turn off the inputbox asking if I want to save unsaved changes.

[Thingol]

Hi there,

I'm kind of struggling with something. When closing an Excel workbook, the application asks if one wants to save unsaved changes. I have a macro that runs when the workbook closes (autoclose). This macro makes one or 2 changes to the workbook. This leads to the application, asking whether you want to save changes or not. This is inconvenient. I would like to turn this off in somehow; is it possible to make code that turns the option for Excel asking to save unsaved changes off?

Thanx,
Martijn.

 

[JustinEzequiel]

the workbook should have a .Saved property
set this to true at the end of your autoclose macro

 

[Thingol]

Thanks! That works fine!

Now, one more problem came up related to this.

This is the code I wrote that is at the end of the autosave sub. It asks the user whether he wants to save or not. I'm not sure how to write the code for the vbCancel option. It should stop the application from closing the workbook, but how can I achieve this?

If ActiveWorkbook.Saved = False Then
msgbox "Do you want to save the unsaved changes?", vbYesNoCancel
If vbYes = True Then
ActiveWorkbook.Save
ElseIf vbNo = True Then
ActiveWorkbook.Saved = True
ElseIf vbCancel = True Then
??????????
??????????
End If
End if

 

[EndoMan]

I assume your "autosave" sub is in a standard VB module. Move your code to the "ThisWorkbook" object module using the BeforeSave event. Then set Cancel to True.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveWorkbook.Saved = False Then
msgbox "Do you want to save the unsaved changes?", vbYesNoCancel
If vbYes = True Then
ActiveWorkbook.Save
ElseIf vbNo = True Then
ActiveWorkbook.Saved = True
ElseIf vbCancel = True Then
Cancel = True
End If
End if
End sub

This should work.

 

[Thingol]

OK, but doesn't this show the msgbox everytime the workbook is being saved? I would only want the messagebox to pop up when the workbook is being closed, and only if the workbook.saved property is false. Is that possible as well?

 

[Thingol]

Ah, I can probably solve it by using the BeforeClose event for the thisworkbook object.

 

[Thingol]

Hm, using the BeforeClose event with the cancel option set to true, doesn't stop the application from closing the workbook. How can I achieve this?

 

[Thingol]

p.s. This is the code that goes along with my question in my previous post.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Opslaan As Boolean

If ActiveWorkbook.Saved = True Then
Opslaan = False
Else
Opslaan = True
End If

If Opslaan = True Then
msgbox "Do you want to save unsaved changes?", vbYesNoCancel
If vbYes = True Then
ActiveWorkbook.Save
ElseIf vbNo = True Then
ActiveWorkbook.Saved = True
ElseIf vbCancel = True Then
Cancel = True
End If
End If

End Sub

 

[SkipVought]

Hi,

I think that your problem is that vbYes, vbNo, VbCabcel are CONSTANTS.

What you need to do is set the result from the MsgBox to a variable and test the variable against those constants.

   MyAnswer = MsgBox("Do you want to save unsaved changes?", vbYesNoCancel)
   Select Case MyAnswer
        Case vbYes
        'my YES code
        
        Case vbNo
        'my NO code
        
        Case vbCancel
        'my CANCEL code

  End Select

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Thingol]

Thanx you all! It all works fine now!!