Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

Install a Macro into an Excel Spreadsheet
This tip will show you how to copy an Excel Macro into your workbook or spreadsheet. You will learn the different l ...
Run a Macro from Another Macro in Excel
I will show you how to run a macro from another macro in Excel.  This means that you can run any macro when you ne ...
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial showing you how to make a user enable macros in a workbook in order to view the workbook. This is very im ...
Automatically Run a Macro When a Workbook is Opened
How to make a macro run automatically after a workbook is opened and before anything else happens. You need this m ...

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?

Right I will try to explain this as best I can as otherwise it could get confusing.

I have written an excel application which is basically a claim form. One major macro of the app is that the all the toolbars and tabs of excel are completely hidden (they are restored on exit). Therefore all functions of the claim are controlled by macro buttons i.e "Save Claim", "Next Page", "Exit Claim" etc etc. I have done this so that before the user can move onto the next page the "Next Page" macro button validates the data they have inputted.

The first version of the form was easily worked around by my users as they just disabled the macro's so I found some code he Code:

which forces users to enable macros in order that the useable sheets are unhidden. I have also added the function in the above code to unprotect the workbook so that the structure can remain protected and the users can not simply manually unhide the sheets if they disable the Macros.

My problem is on the saving of the form: This workbook is to be considered a master copy so the Save buttons are "Save As" so as not to overwrite the original. Only trouble is that I need to add the code to re-protect the structure and rehide all the sheets so that the saved copy requires them to enable Macros aswell and start from the first sheet. I cannot for the life of me seem to get it right my save code is simply as follows which I know is not enough:


Sub Save()
Dim Fname As Variant
Fname = Application.GetSaveAsFilename("", "Workbook (*.xls), *.xls")
If Fname = False Then Exit Sub
ActiveWorkbook.Saveas CStr(Fname)
End Sub

If anyone can help that would be great. I need to make sure that when the workbook is saved or the option to cancel the save is chosen then the current workbook will go back to the same state as it was before the Save button was pressed. The actual saved copy should open in the same way as the original.

Hopefully someone will be able to help me. Please go very easy on me and keep it simple as this is all very new to me.

If necessary I can upload the workbook. Thanks in advance.

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 All,

I am creating a workbook that I need to create a macro that when I click the button it will save the workbook and then exit the workbook and application

I am using

ThisWorkbook.Close savechanges:=True

The problem I am having is that if I have other instances of excel open it closes them too. How can I only close the workbook and instance that I have the exit macro in?

hi all

just need a little bit of advice i have a spreadsheet done in excel 2002 it works perfect on my computer it has 1 macro and a button which i have assigned the macro to but when i send this to a freind in manchester he dosnt get the button in the toolbar he can use cntrll+c but when i save the workbook it dosnt give me the option to save as macro enabled like excel 2007 does any ideas ?

thanks in advance

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.

Hi Folks,

I'm completely stumped at this one. I have the following code which runs everytime someone saves the workbook, the code works fine when the "Save" button is pressed, but I have hit a problem when the user chooses "Save As". When save as is chosen I would like the user to be able to choosea filename like excel normally allows, then let it run the normal save function. And ideas???



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

Dim thisWS As String
thisWS = ThisWorkbook.ActiveSheet.Name

If SaveAsUI = True Then

    MsgBox "Save As"

    GoTo SaveOnly
End If

Exit Sub


Call WBK_SecurityUnlock 'Unlock WBK Security

Application.EnableEvents = False
Application.ScreenUpdating = False

Call HideAllTabs

Call ShowRequiredTabs(thisWS)
ThisWorkbook.Saved = True

Application.ScreenUpdating = True
Application.EnableEvents = True

Call WBK_SecurityLock 'Relock WBK Security

Cancel = True

Exit Sub

End Sub

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?)

Hi All,
First up thanks for any / all help.

How do I have excel run a macro / module everytime the X button is pressed or File>>Exit is chosen with the mouse.

Just need excel to run a save macro (in a different directory for backup purposes) before it quits.

Thanks again

Hello all,

I have a question that I hope someone can guide me to a resolution regarding using the save function in a workbook.

My workbook has a "Macros are disabled" start page.
(a snippet of my current code)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Sheets("macro1").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
    If ws.Name  "Macro1" Then ws.Visible = xlSheetVeryHidden
Next ws

So all my pages are hidden on exit (open) except the macro page. The macro page explains to my teachers that macros must be enabled (and how to enable) or this program won't run correctly.

This works find and dandy, however, if a teacher screws up, they usually just close the program and re-open it. That's where my problem comes into effect.

Is it possible to enable an application macro - if the user clicks the little save icon in the tool bar or clicks save under menu File this macro will run?

If I can do this, I can put the "Macros are disabled" code in the "save" macro and remove the beforeclose, so if the teacher screws up, they can exit / re-open the workbook and not have saved their screw up.

I though of having a Save Button, but the screen would become too cumbersome.

Thank you for your time in reading this and your feedback.

Teacher Eric

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.