Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Save And Exit Macro

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I need a macro that will give the option to save the workbook and exit when a button is pressed. The sheet will be protected.

Any ideas?

View Answers     

Similar Excel Tutorials

Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...
Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Countdown Timer in Excel
How to create a countdown timer in Excel. This allows you to display a timer in whatever cell you want and it will ...
Macro to get Data from Another Workbook in Excel
Macro to get data from a workbook, closed or open, over a network or locally on your computer. This is a versatile ...
Prevent Excel Alerts and Messages Appearing While Running a Macro in Excel
How to stop an Excel alert window or message box from appearing while running a macro. This is particularly useful ...

Helpful Excel Macros

Disables the "Save As" Feature in Excel
- This macro will disable the Save As feature in excel. This means that a user will not be able to resave the workbook un
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when

Similar Topics

I have several buttons in a workbook, each with a unique macro assignment. However, the spreadsheet seems to be forgetting the macros assignments (for all macros) after I save/exit the file.

The next time I open the file and try to run the macro, I get an error "((#REF.xls could not be found)). I even reassign the macro to the button, test that it works, then save/exit and get the same results when I reopen the file.

Any ideas?

Is there a way to write a macro to save over a write protected file which I know the password for? Alternatively, can i simply disable the write protection on the file with the macro before saving it to another file name?

My situation is that we have a file which is updated daily but is a monthly workbook. This file must be write protected. At the end of every month, some changes need to be made with a macro and then it needs to be saved to an archive folder before it is cleared for the next month. This would not be a problem for the first year. However, the second year, when the files for each month already exist (i suppose if I can't solve this I can just remove them yearly) I will get an error that says I can't save over the file because it is write protected. I only get this error when saving through the macro and do not get it if I save manually.

I was thinking that if I can't do that, then perhaps I could disable the write protection with a macro and then save it to the archive folder so none of the archived files will be right protected. This is certainly the less optimal option.

Hi everyone!

I am a novice when it comes to Macros. I have created a button in a spreadsheet. My intentions are for this button to save a file to a specific location with a file name derived from a cell. Below is the code that I am currently working with:

'error trap On Error GoTo Etrap Dim MyCell MyCell = Range("B2").Value 'ask user to save If MsgBox("Save new workbook as " & CurDir & "\" & MyCell & ".xlsm?", vbYesNo) = vbNo Then Exit Sub End If 'check value of activecell If MyCell = "" Then MsgBox "Please check the Cell Value", vbInformation Exit Sub End If 'save activeworkbook as new workbook ActiveWorkbook.SaveAs Filename:=MyCell & ".xlsm", _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False Etrap: Beep Exit Sub

End Sub

Please Help!

I have a workbook consisting of several worksheets that users input various data. I have a command button (SAVE button) and I want after the user inputs the filename to have the option to close the workbook and exit or have it remained opened. The file will be saved in the same directory that had the original file.

I have the following code:
Sub Save_Click()
'To save file and provide option to close the file
Dim FileName1 As String
Dim Close1 As String

FileName1 = InputBox(Prompt:="Please enter file name")
If FileName1 = "" Then
MsgBox Prompt:="You did not enter a filename", Buttons:=16
FileName1 = InputBox(Prompt:="Please enter file name")
End If
ActiveWorkbook.SaveAs Filename:=FileName1
Close1 = MsgBox(Prompt:="Do you want to exit the file?", Buttons:=4)
If Close1 = vbYes Then ActiveWorkbook.Close
End Sub

1. The file does not save with the xls extension. How do I incorporate code so that it does. Also I don't the user to input the extension. If they do, is there some way to ignore it so that the filename does not take on the format as ABC.xls.xls?

2. If the user were to input the same filename when the save button is selected and is prompted by excel if they want to replace the existing file and if no or cancel is selected - the following error is displayed

Run-time error '1004':
Method 'SaveAs' of Object '_of Workbook failed.

When I hit debug, the following line is highlighted in yellow? Why?

ActiveWorkbook.SaveAs Filename:=FileName1

Help is greatly appreciated.

Thank you.

I have a macro that automatically closes out of all open workbooks; however, I want it to close without saving the read only documents. Right now it closes out of everything, but a pop up appears asking if the user wants to save the read only worksheet.

I don't want that pop-up to appear. I want it to just exit out and save all spreadsheets except the read only ones WITHOUT asking.

Here is the current code:


Sub CloseOpenWorkbooks()
    Dim Wkb As Workbook
    With Application
        .ScreenUpdating = False
        For Each Wkb In Workbooks
            With Wkb
                 ' If the book is read-only
                 ' don't save but close
                If Not Wkb.ReadOnly Then
                End If
                 ' save this workbook, but don't close it
                 ' so the macro still runs
                If .Name  ThisWorkbook.Name Then
                End If
            End With
        Next Wkb
        .ScreenUpdating = True
    End With
End Sub

Hi, I want to disable all save option's in a workbook but still allow 2 macros to save the file.

Im using this to disable the save option:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If SaveAsUI = False Then
Cancel = True
MsgBox "You cannot save this workbook. Use Save As"
End If

End Sub

The macros I use to save the book are called Macro1 and Macro2

Kind Regards,


I have a workbook with a BeforeSave macro. It works fine if you run another macro before the first time you try to save and works fine if i press the save button a thousand times (i assume, haven't actually pressed it that many times), but if i press the save button and then run another macro, when i press save again the macro doesn't run.

Does anybody know why???



Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Cells(1, 4) = "" Then
    MsgBox "Sheet must be validated first"
    Cancel = True
ElseIf Cells(1, 4) = "Validated" Then
End If
End Sub

I have a cancel button in a userform. The code in the form unloads the
userform. But I want the cancel button to end the main macro too. I've
tried numerous conbinations of

if cbCancel then exit sub

and a dozen others. None of them work. They either cancel the program
even if the Cancel button isn't clicked, or don't exit when it is. I
did a google search here and found a number of routines, none of which
seemed to apply to my situation (I don't need to check and see if the
person speaks English to exit. I want my users to be able to exit even
if they don't speak English!). Can someone give me some simple code to
put in my macro to close the program if the cbCancel command button has
been clicked?

Thanks for the help!

I have a macro enabled workbook with a file name that starts with the word "data".

I input data to the workbook through the use of a user form. On the user form I have a command button that I click periodically to save my work. (I have the ability to hide the workbook and added the button for this reason)

There are several time I need to open an additional workbook or two to do research and if I click the save button on the user form and the "data" workbook is not active we get a run time error.

Right now I have just the standard macro instructions for the command button:

>Private Sub CommandButton3_Click()


>End Sub


I have made a sheet that I have to give access to other users as well. But I have protected cells in the sheet so that the formulas can not be altered. But when I do this, macro button stop responding. In fact, they become unclickable. I tried moving the buttons to the unprotected cells but as the macro is working on the part of sheet which is protected, so it is not working.
I want user to be able to click on macro button and have access to some cells of the sheet but the rest of sheet should stay protected.

Can somebody help me please?

Hello all,

I have an excel workbook that links to a SQL database to update. I want it to do the following:

1 - open at a scheduled time
2 - refresh
3 - save
4 - save as a pdf
5 - close

I can get it to open with Windows Task Scheduler. I can get it to refresh with a macro. I can get it to save and save as a pdf with a macro. I have not been successful at getting the macro to auto run when the workbook opens, and get it to delay the save until the refresh finishes. if the 'save' kicks in before the refresh finishes, excel pops up and asks if i want to cancel the refresh. I tried using the "Application.Wait (Now() + TimeValue("00:00:30"))" to stall the next phase of the macro until the refresh has completed, but it stalls all action in the sheet, including the refresh.

Any ideas?
- Tom

Hi Everyone,

Is it possible to write a macro that will save the current worksheet I am working on as a NEW tab on an existing file? ie, Clicking on a run macro button will bring up the "save as" screen, allowing the user to pick the file into which he/she wants to save the current workbook. Then, it will automatically create a new worksheet in that file, copy the data in the open workbook to that worksheet, save, and then close. (This will also take off all macros since only the data in the spreadsheet will be copied.) Thanks in advance!

I have a spreadsheet that automatically saves itself when you close it (it doesn't prompt the user) as I must make several sheets hidden upon exit. I can't have it prompt the user the normal way as they may chose not to save and of course the sheet state wont then be saved on exit.

My thinking is to setup a basic 'Do you want to save?' Yes/No form upon exit and have the user pick accordingly. If they chose Yes, then it just saves, easy ...If they pick No then it undoes everything modified on the spreadsheet since the last manual 'Save' (They may not have saved it, they may have just opened it and changed a few numbers here and there, both are possible)

Looking at some of the 'undo' code on the internet I'm guessing it's not possible as you have to store every change in order to be able to change it back, right? Perhaps it's possible to somehow use 'SaveAs' and create a temporary copy and somehow revert back to the original one upon exit (if they choose No) ...I don't know I'm just thinking aloud I guess.

If you have any thoughts or suggestions they are very welcome.

Many thanks


I am working with Excel 2007. I am unable to save my spreed sheet and I think it's because the macros. Every time I hit save I get this message:

The following features cannot be saved in macro-free workbooks:
- VB project
To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes.

I googled this problem and then tried to save it in 97-2003 workbook but my spreed sheet is too big and I lose data. Is there an option where I won't lose data but can keep the macro? Sorry, as I am sure this has been asked but I have been unable to find a solution

Hi - I have created a small macro and assigned it to a macro button that is placed on the Reviewing toolbar. When the button on the Reviewing toolbar is pressed, the macro is running fine and performing the job that I desire for.

When I shared the excel sheet with one of my friends, he could not see the newly created macro button whatsoever However, he could see the macro code in the VB editor.

May I please know why the other users are not able to view this newly created macro button, while it is very much visible to me? For your information, the workbook is not protected. No security is present.

Please help. I am an amateur in excel and the question also might look silly. But, a solution to this problem is much appreciable. Thanks.


Hey all,
Trying to make a command button that saves the workbook and then closes.
I have this code and it works somewhat. I want to save and close without the save dialog popping up asking is I want to save the changes, as the workbook has already been saved in the macro. Is there a way around this?

Private Sub CommandButton2_Click()
End Sub

Hi All,

I has an exit event in a textbox on a userform that sets focus on a combobox.

I have a 'Exit' button on the userform as well.

I simply need to say in the textbox exit event to setfocus on the combobox, unless the exit button is pressed.

Something like


private sub textbox1.exit (ByVal Cancel As MSForms.ReturnBoolean)
if not = True then
end if
end sub

But I don't know what the correct way to say = true is.

Thoughts ladies and gentlemen?

Can you hide a worksheet menu bar ?

I have a PROTECTED Worksheet for employees where they click on a macro button to save the worksheet in read only so it can't be accidently deleted, and it is also saved in a backup folder.

But I dont want employees to be able to click on Save in the File Menu, only the Save Macro button I have created.

Is this possible


I am using a userform in which i have an exit button which removes some sheets and exits after saving the workbook.
I am also using the same thing as an workbook before close event but when i exit using button it also displays the befoeclose event message

see the code below


Private Sub Workbook_BeforeClose(Cancel As Boolean)
resp = MsgBox("Do you want to remove the RAO Sheets?", 4)
If resp = 6 Then
    Workbooks("Motion Analysis.xls").Close savechanges:=True
End If
End Sub


Private Sub CmdExit_Click()
If (Sheets("sheet1").Cells(1, 3) = 1) Then
    ans = MsgBox("The Data Will be Erased" & vbCrLf & "Have you saved The Data", 4, "Exit Application")
    If ans = 6 Then
        Workbooks("Motion Analysis.xls").Close savechanges:=True
        MsgBox ("Please Save Your Data")
    End If
    Workbooks("Motion Analysis.xls").Close savechanges:=True
End If
End Sub

I want that if exit btn is used the beforeclose event is disabled

Hi Im hoping someone could help me out here...what Im looking for is help with disable the "save" option in Excel.

Ive got this bit of code


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "The 'Save As' function has been disabled."
Cancel = True
End If
End Sub

This works fine when I select the "Save As" option, however I also want to disable the "Save" option from the file menu and the "Save" button.

Any help or guidance is much appreciated.

Also I am using Excel 2007.

Wonder if there is a way (code) to program a Macro button on an excel spreadsheet that once clicked would save and close the workbook.

would be good also if I could maybe have two different buttons, one to save , one to close.

Im very much a newbie chump and have no idea how to write code, just copy and past is the name of my game.

Thanks in advance!

Dear all,

please please help for below requirement

I want excel not to save unless i have A1 = 2, that means i want validation for number 2 in A1 cell before saving, if A1 2 then it will give message " Data not valid" , please... please help

juzar para


I have a large workbook which is mostly protected so that users cannot change it. I have also disabled all of the save/exit options to keep them in my control.
As a result I have had to create a number of small macros which allow me to unprotect everything/save the workbook etc. The problem is that these macros are visible to user via the Developer tab, so they could run them and access the sheets.
I want to put a password on these macros, so that if anyone tries to run them, they need to enter it. If I create a simple userform asking for a password, is it possible to mask the text?
Or is there a better way to restrict access to macros?


Is there a macro that will select the save button say every 10 mins or so so that I don't lose any work by accidently closing the workbook and forgetting to save?

I have a working macro that opens a workbook, does some stuff, and then closes it. But, when I run the macro, it prompts me as to whether or not I want to save changes. Is there a way I can write into the macro not to save the changes?

Currently the code I'm using to close the workbook is the following:


If you know better code, and also a way to tell it not to save changes, I'd be very appreciative!