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. ...
Prevent Saving a Workbook under a Different File Name
This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can ...
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 workbo ...
Reverse Row or Column Order in a Worksheet
This macro reverses the order of a selected row or column in excel. It will completely reverse the data. This is d ...

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'd like my button-triggered macro to exit and quit IF the sheet it is on is protected when someone presses the button. Here's the idea:

IF the active sheet protected THEN
Display "Can't do this on protected sheet" (and exit macro after click OK)
run the rest of the macro.

Syntax for this has me bogged down. Any help will be appreciated.


When I exit excel I don't want it to ask if I want to save changes. I currently have a command button to exit;


Private Sub CommandButton103_Click()
End Sub

Most of the help I've seen details using a workbook event. I could not get this to work. I want to close the whole excel application anyway not just the workbook.

Can anyone help with an additional bit of code to add to the above to stop the save request on exit?

I'm a vba novice so keep it basic please!

If it needs to be a seperate pice of code, how do I link it to the exit button?

Any help very much appreciated.

hey all,

i would like to add and "save and exit" button to my spreadsheet to ensure it opens in the same format every time it is reopened. I've added a macro that formats the spreadsheet how i want it but am struggling to get it to close??

any ideas?



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?

I have a spreadsheet used by several users. I want to make sure that if they make any changes, they use the File=>Save As option and and increment the version number in the file name. In order to prompt this, I pictured a dialog box similar to the one that says "Do you want to save changes" that appears on Exit?

I'm assuming I need a macro but being a Macro virgin, I'm a bit stumped where to begin.

OK - the details. If the user clicks File=>Exit, or the Top RH Corner X, I want a dialog box thats says "If you have modified this file, please ensure that you use File=>Save As option, and save as a higher version" I then picture the user having two buttons: "Return to Excel" (which cancels the Exit procedure and returns the user back to the workbook) and "Continue to Exit" (which allows the exit procedure to continue)

For info, I'm on Excel 2003 SP3 running on XP Professional 5.1.2600 Service PAck 2.0

Any help or advice gratefully received

My new Excel workbook is always slow to open, save or exit, sometimes taking as long as 3 minutes. The file is only 733 Kb, but it does contain about 9 macros. Saving the macros to a personal macro workbook is not an option, as the workbook will be shared and used on many computers.

Although the macros are recorded, I did go back and edit them to clean them up, removing uncecessary scrolling and stuff. I'm sure they're not as efficient as macros written in VBA, but I don't know VBA. The workbook is also chock full of formulas, but that is unavoidable.

Any ideas what is causing the slow load and save times, or suggestions to prevent it, would be greatly appreciated.

Thanks in advance.



I want Excel not to prompt for Save on Exit but to close, I have tried a few different codes but they will only work if I test them in VBE, when the page is reopened it defaults back to requesting save. ( I am saving the code in VBE before exiting)

I'm not sure if it has anything to do with the fact I have a macro linked to a button which closes the document without saving. I would like it so that if the person click X then it would not allow them to save the SS. I am using 2003.


Hi guys

As you can see in the attachment file, I have a form from which I want to open a report so I have put a button "View MI Report". when this button is clicked then a worksheet opens. What I want is when user clicks on X button on the worksheet then the worksheet should be closed and form should be opened. You can see code of workbook_beforeclose event in the file attached.

And for save & exit button , When the user clicks on this button then the whole project workbook should be closed. But in my case Workbook_beforeclose event is interfering with save & exit button code.

I want to perform two tasks as discussed above in the right order. I have tried application.quit in save & exit button code too but it doesn't give me the right result.

Could anyone please help me out


Hi Everyone!

I would like to have a macro code to Save the workbook (same file name) and then Exit. It is equivalent to File Menu and then selecting Exit.

I appreciate everyone's help.

Thanks again in advance.



i want my workbook to have a custom 'YES NO' box on exit instead of the Excel Save Changes? box. I have my exit box working fine but when i click NO excel closes down, when i click yes the box reappears and i have to click YES twice. the box is called by workbook_beforeclose


Dim ans As Integer
Sub exitbox()

ans = MsgBox("Do you want to Exit the calculation selector?", vbYesNo, "Exit")
Select Case ans
    Case vbNo
    No = True
    Exit Sub
    Case Else
End Select

    If ans = vbYes Then

    ''''saves visible properties of each sheet

    ThisWorkbook.Close Saved = True
    End If

End Sub

any ideas?

EDIT: also when i click no the save changes box still appears? i have turned off application.displayalerts before the exitbox sub runs in the sub 'workbook_beforeclose'

Hi Guys

I am really sorry my question has become lenghty.I have finished my project and at the end a strange problem is coming up. In the main form I have a button Save & Exit and I have written following code on it.

Unload Me

and in the main form there is one option "View database" which shows to the authorised user all sheets. When I click on this option then workbook comes up with all sheets displayed.To close this I press X button at the top and so I have written the following on so that when I press on X button then main form should come up.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Visible = False

UserForm4.Show vbModal
Application.Visible = True
end sub

The problem coming up is now when I click save and exit button on main form then it closes the project but after few seconds again it appears. I think this is because of workbook before event.

Could anyone solve my problem. My project should stop running once I cick on save & exit button. and when I click at X button of workbook then main form should come up.


I'm creating a Userform for a data entry project and I used the following code that I found online to create a "Exit Form" button:

Private Sub cmdclose_Click()
Unload Me
End Sub

Using this code will let the user exit the form, but it doesn't save the spreadsheet when they do. I'd like to make it an Exit and Save function so it does both actions when they click on the exit button. Any suggestions on what code I could add to have the button function in this manner?


I have a macro in which you push a button and it automatically pops up the save as, however my problem is that I don't get to choose what type of format I want to save it as. It only gives me the option of ALL FILES.

So my code is application.saveasfile. - how can I change this so it will give me the option to save it as an excel document (give me the dropdown of choosing what type of format I want)

Thank you for your help.

I am new to VBA and macros and have learned a lot searching this forum but I have a problem that I cannot find the answer to in previous threads. I will explain what I am trying to do, what I know how to do, then what I don't know how to do. This way if there is a better solution you can suggest one.

I have multiple excel 2010 spreadsheets loaded in one directory on our departments SharePoint. There are various users that access these files, they enter data save it and exit. The files open up as server read only, so they have the big yellow bar across the top that says "This workbook was opened from a server in read-only mode" and an "edit workbook" button that you can push and then click the save button to save the file without being prompted where to save it with a "save as" dialog box. My problem is that some users do not click the edit button and are then prompted with a save as box when click save,then they click the wrong file name and overwrite another workbook from the directory.

I want to create a button on the sheet that launches a macro that saves and closes the workbook. I have found this code here and have it working. The problem is the edit workbook button. some users click it and some do not. if I use a ActiveWorkbook.LockServerFile command, the macro will push the button for me but if the button has been clicked by the user, the script fails. I don't know how to write an if then to fix this

I am setting up this macro to automatically bring up the Save-As box upon opening.
Here is the code I have:

Application.DisplayAlerts = False
        template_file = ActiveWorkbook.FullName
        fileSaveName = Application.GetSaveAsFilename( _
        InitialFileName:="S:\SERVICE\Shop Teardown Reports\Job-Number teardown" & ".xls", _
        fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
    If fileSaveName = False Then MsgBox "Save-As has been cancelled!"
    Exit Sub
        Application.DisplayAlerts = True

To test it I just have it going to a button and as of now, the box will come up and go to the correct folder, I can type the filename I want, but when I hit save it seems to just exit out and not do anything. The workbook name does not change and the file is not saved (from what I can tell).
So what am I missing here???

The cancel message box is working correctly.

Thanks yet again.

I am currently running office 2007 and I want a macro that will save the current workbook and then close it but not exit the program. I am currently using the following code:

The problem I am having is that when the program finishes the save and closes the workbook, Excel 2007 crashes and then executes and auto relaunch. Is there anything I can do with the code to prevent this from happening?



Excel Workbook with digitally signed macro (important?)

Workbook in Excel
- Macro works as expected

Embed workbook in Word

Enter Excel, change data, run macro, exit Excel
Enter Excel, change data, run macro, exit Excel
Enter Excel, change data, run macro, exit Excel

Save Word document
Exit Word
Start Word
Open document

Enter Excel, change data, run macro, get message:

Microsoft Office Excel cannot access the file 'Worksheet in C:'
There are several possible reasons:

The filename or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook.

Excel and Word are 2007

I'm I doing something wrong or is this a bug in a tux? (An unintended feature?)

Is it possible to force a user to save on exit. I added a button to run save and exit but they are still closing with the x.

I want to add a command Button to a worksheet that will, when pressed, save the worksheet into a predetermined location in a format like Mike06March2007. I can get the Save button to save itself with but I am stuck for the rest. Any code I have already used from the internet generates an error. Any ideas would be welcome.

I am just wondering if there is any way that I can get some macro to run automatically when I click on the save button of the Excel save file dialogue? Basically, I need to set all worksheet as "Protected" status when I click on the Save button and all should be done automatically.


Hi again everybody!

I have been going along just fine with all your help, and guess what: New issues!

I've been writing macros (via Excel Macro recorder) and tweaking them with your help to VeryHide every single sheet except the one that the macro has directed the user to.

If the guy uses the macros to exit and close the program, the data is saved and the file re-opens on sheet1, the one it's supposed to.

What happens now is this: If I click the save button or use the File/Save route and then exit the file via File/Close (or the close button), the sheet that the user was on when he saved the file is opened with Sheet1 when he opens the file again.

I do not want that!

What can I do to prevent him using the above ways, and also the Save As way?

What can I do to close a worksheet as VeryHidden at all times if it's not the active sheet? What can I do to ALWAYS have only sheet1 open up on startup?

I gotta learn ta code, don' ya tink?

These late nights are interfering with my cycling. I'm going to bed now!

Sleep well all of you!



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.

Im looking for a code that i can add to a command button that will autosave the workbook and exit the whole of the excel application.
Thanks for any help in advance

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!

Macro to remove the option to save as if file was open as read only?

Running Excel 2003 with Windows 7

I have a workbook that I have set up with security to recommend open as read-only and if not there is a password that has to be entered so changes can be made to it. But when I open it as read-only and try to close out it give me an option to save-as. Is there a way to remove this option so when everyone is closing out it just closes it with out giving anyone any options to save or save-as when it is opened as read-only?