stop Pop Up do you want to Save?

phillipclark

Board Regular
Joined
Sep 10, 2013
Messages
65
Good afternoon All,
I am using the below code when the close button is used to ask the user a series of questions based on yes or no answers. This works fine and I have no issues. The problem i have is that excel will have the pop up asking the user to save the file. I am already using a macro and custom file name so i need to disable all of these request. What do i need to add to my code below in order to prevent this request from excel.

Thank You in advance for all your help.

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Have you saved the data?", vbYesNo) = vbYes Then Cancel = False
If MsgBox("Would you like to cancel close?", vbYesNo) = vbYes Then Cancel = True
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
Maybe:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If MsgBox("Have you saved the data?", vbYesNo) = vbYes Then Cancel = False
    If MsgBox("Would you like to cancel close?", vbYesNo) = vbYes Then Cancel = True
    ActiveWorkbook.Saved = True
End Sub
 

phillipclark

Board Regular
Joined
Sep 10, 2013
Messages
65
Good Morning Mumps,
I have tried this solution and have had no success. It will only work if there are two workbooks open. If this workbook that has the code is the only one open at the time of closing. it still asks me to save.
Also adding the me.saved line does not work to suppress the pop up

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Have you saved the data?", vbYesNo) = vbYes Then Cancel = False
If MsgBox("Would you like to cancel close?", vbYesNo) = vbYes Then Cancel = True
 Me.Saved = True
 
End Su
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
I have tried it with only the workbook that has the code being open and it works properly so I don't know why it isn't working for you unless another macro is somehow interfering with it.
 

phillipclark

Board Regular
Joined
Sep 10, 2013
Messages
65
Its possible that there is something else interfering with it. I will post the other code in the work book below. Take a look and see what you think.
This code clears contents from cells in the workbook when it is opened and is in sheet three

Code:
Private Sub Workbook_Open()
     'change C6 to your desired cell
     
    Range("C4:C12").Select
    Selection.ClearContents
    Range("C21:C30").Select
    Selection.ClearContents
    Range("C3").Select
    Selection.ClearContents
    
    
End Sub
This code closes the workbook from an icon on sheet three it works fine and doesn't generate any messages
code is in Module 1

Code:
Sub SaveIt()
Dim dt As String, wbNam As String
wbNam = "Cookie Length Chart_"
dt = Format(CStr(Now), "yyyy_dd_mm_hh_mm AMPM")
ActiveWorkbook.SaveAs Filename:=wbNam & dt, FileFormat:=52
ActiveWorkbook.Close
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
It doesn't appear that the code you posted would interfere. Perhaps you could upload a copy of your file that is giving you the problem with all the macros to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. De-sensitize any confidential data.
 

phillipclark

Board Regular
Joined
Sep 10, 2013
Messages
65
Ok here is the link to download the file from Drop Box. The first tab is the main display. No information is entered here it simply uses the camera function of excel to display the charts from the information entered on tab length info. Just to look pretty lol. Click the save icon on the info page and it saves a file with a specific name and format. Close the file with the x button and this is what causes the automated save question that needs to be removed

https://www.dropbox.com/s/kze4hc72zrk9fvk/Cookie Length Chart Blank REV 1A.xlsm?dl=0
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,991
Office Version
365
Platform
Windows
Using the code in post#3 it works fine for me in xl2013
 

Forum statistics

Threads
1,081,545
Messages
5,359,438
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top