Email:      Pass:    Pass?
Subscribe for Free Excel tips & more!
E-mail:
Advertisements


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?


Similar Excel Video Tutorials

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
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
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
Protect and Unprotect All Worksheets at Once
- This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a passwor
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

Similar Topics







Hi,
Is it possible to have a macro which when clicked on will auto save a
workbook (and selecting the option for 'yes' when asked if you want to
replace the file already saved on the hard drive with the same name), then
exit the program ??

Any ideas??

Rgds

Anthony



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)
Else
run the rest of the macro.

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

Thanks!


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;

Code:

Private Sub CommandButton103_Click()
Application.Quit
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?

thanks,

Sean


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:

http://www.dotxls.com/excel-security/28/how-to-make-a-user-enable-excel-macros


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:

Code:

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.


Hi,
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.

Jerry


Hi,

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.

Thanks


Hi,

I have included the following 2 lines of code

msgbox ("Running of a Macro empties the UNDO stack. You should save your
workbook with a different version number before running this Macro. Press
Yes if you would you like to exit out of the macro and save your workbook.
Press No if you have already saved the workbook and would like to continue
with macro execution", vbOKCancel, "Warning") As msgboxresult
If msgboxresult = "Cancel" Then Exit Sub


When running the above code it bombs at the line - msgbox ("...") As
msgboxresult
The error im getting is -- compile error : statement invalid outside Type
Block

Please guide me as to what am doing wrong

--
Thanks a lot,
Hari
India





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

Regards
Raj


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

Application.Quit
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.

RNF

Sheet Compile Print Save PDF
Sheet 1
Sheet 3
Sheet 4
Sheet 5
Sheet 7

Execute

I have a sheet that looks like the above. There is a check box for every row/column.

For instance, the user has the option to check mark - sheet 1 (compile, print, save) and sheet 4 (compile save, pdf)

When the execute button is pressed. I would like a macro to scan for all the boxes that have been checked and run a specific macro for the selected buttons.

If the following is checked:
sheet 1 (compile, print, save) and sheet 4 (compile save, pdf)

Then

Execute

Then

Run Macro Sheet1Compile, Sheet1Print, Sheet1 Save; Sheet4compile, sheet4save and sheet5pdf.


Thanks


hello,

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

Code:

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
    SaveStateAndHide

    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.
Code:

Unload Me
ActiveWorkbook.Save
ActiveWorkbook.Close


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.
Code:

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.

Thanks
Aman


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?


Hi all,

Just another quick question regarding some macro code I have.
I have 2 buttons, each with their own macro.

Button/Macro 1 is:

Public Sub SaveAsC6()
ThisFile = Range("C6").Value
ThisFile2 = Range("E5").Value
ActiveWorkbook.SaveAs Filename:=ThisFile & "-" & ThisFile2 & "-" & Format(Date, "yyyymmdd")
End Sub

When clicking this Macro, it saves the file, using the data in cells C6, E5 and today's date as the filename.

What I would like to do is add a message box that comes up to confirm to me that the save has been successful after the button has been pressed/saved. Can anyone tell what code I need to add?
The message box can say something along the lines of 'Save Successful'.



Button/Macro 2 is:

Sub CloseWorkbook()
ThisWorkbook.Close Saved = True
End Sub


Upon pressing this button, Excel closes the workbook. However, before closing it, I would like a message box coming up, asking me 'Are you sure you want to exit'?

Thanks!




Hi

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:
Code:

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

Thanks
Ryan

Code:

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 'NEED IT TO DO THIS ONCE IT HAS BEEN SAVED AS A NEW FILE.
    
Else

    GoTo SaveOnly
    
End If

Exit Sub

SaveOnly:

Call WBK_SecurityUnlock 'Unlock WBK Security

Application.EnableEvents = False
Application.ScreenUpdating = False

Call HideAllTabs
ThisWorkbook.Save

Call ShowRequiredTabs(thisWS)
ThisWorkbook.Saved = True

Application.ScreenUpdating = True
Application.EnableEvents = True

Call WBK_SecurityLock 'Relock WBK Security

Cancel = True

Exit Sub

End Sub





Is it possible to save only one sheet of data.

For example, I will have a workbook full of data and macros to perform a task by a user etc, but once a user is finished collecting their data, all that is needed to be saved is what is collected into one sheet.

So on exit of a certain workbook I want the following to occur.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
*Save location is current workbook name* Only save the data on sheetX Do not save workbook macros or any other sheet(s). (in other words, only save sheetX) End Sub


Any help appreciated, Thanks.


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:

Activeworkbook.save
Activeworkbook.close

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?

Thanks,

Robert