Disable "Do you want to save changes" MsgBox



I have a Command Button that when clicked makes a copy of some data to
another worksheet then saves the active worksheet based on the name in cell
C2. See code below.

My problem is when you close the workbook you get the message "Do You Want
to Save Changes You Made?"
I would like to prevent that message from appearing. How can I do that?


Private Sub CommandButton4_Click()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Range("C2").Value
Application.DisplayAlerts = True

'rCell Makes a copy of the initial calculations and saves to the Data
'rFound looks for a duplicate date and if found copies over it else copies
to next avail row
Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
..Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B17:O37").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
End Sub

Jake Marx

Hi Tim,

The Saved property of the Workbook object tells Excel whether or not a
workbook is "dirty". You could try this:

ThisWorkbook.Saved = True

Keep in mind that if the user makes any other changes, it will become dirty


Jake Marx

[please keep replies in the newsgroup - email address unmonitored]

Simon Lloyd

Hi, at the end of your code you can add Activeworkbook.Save as long a
you do not mdify a cell or worksheet after this you will be able t
close it without the pop up, after Activeworkbook.Save you coul
Application.Quit where Excel would close.



You could also add this under a workbook_before close event:

Application.DisplayAlerts = False
Application.DisplayAlerts = Tru

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question