Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it possible to disable "save" prompt in excel file on web server? | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
DISABLE "SAVE QUERY DEFINITION" | Excel Programming | |||
DISABLE CLOSE MSGBOX ON CLICK "X" | Excel Programming | |||
Disable "Save" &/or "Close" | Excel Programming |