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?

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:=(.Wor ksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Off set(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

Disable "Do you want to save changes" MsgBox
 

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]


TimN wrote:
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:=(.Work sheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
.Worksheets("Data").Range("A65536").End(xlUp).Offs et(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

Disable "Do you want to save changes" MsgBox
 

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

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=57155

Disable "Do you want to save changes" MsgBox
 

You could also add this under a workbook_before close event:

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

--
kev_0
-----------------------------------------------------------------------
kev_06's Profile: http://www.excelforum.com/member.php...fo&userid=3504
View this thread: http://www.excelforum.com/showthread.php?threadid=57155