Disable "Do you want to save changes" MsgBox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?

Thanks



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
worksheet
'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)
Else
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
 
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
again.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]
 
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.

Regards,
Simo
 
You could also add this under a workbook_before close event:

Application.DisplayAlerts = False
Thisworkbook.Close
Application.DisplayAlerts = Tru
 
Back
Top