Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Macro to Close Excel

Posted on 2011-02-11
10
Medium Priority
?
335 Views
Last Modified: 2012-05-11
Experts,
I would like to create a command button that runs a macro that when selected closes Excel, asking to save changes, if changes have been made.
The name of my Workbook is Monthly
0
Comment
Question by:Frank Freese
  • 4
  • 4
  • 2
10 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 34875230
Here's the code -
Close-Me.xls
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 34875237
The code used is


Sub closeme()
ThisWorkbook.Close
End Sub
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34875240
Is this what you want?
Dim strMsg As String
Dim intQ As String

strMsg = "Do you want to save changes?"
intQ = MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes?")

If intQ = vbYes Then
    ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename, FileFormat:=52
End If
ActiveWorkbook.Close

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Frank Freese
ID: 34875310
jp: close...real close...I like MsgBox...If the user wants to save changes can I execute a ActiveWorkbook.Save, ActiveWorkbook.Close, and ActiveWorkbook.Quit (not Activeworkbook.SaveAs)
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34875347
You can make a .SaveAs or .Save

I don't understand your question... sorry.
0
 

Author Comment

by:Frank Freese
ID: 34875423
Here's my code. My objective would be to save the file, close the program and exit excel:

Sub CloseExcel()

Dim strMsg As String
Dim intQ As String

strMsg = "Do you want to save changes?"
intQ = MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes?")

If intQ = vbYes Then
ActiveWorkbook.Save Filename:=Application.GetSaveAsFilename, FileFormat:=52

End If
ActiveWorkbook.Close
ActiveWorkbook.Quit
End Sub
0
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 34875467
Like this should do it:

Sub CloseExcel()

Dim strMsg As String
Dim intQ As String

strMsg = "Do you want to save changes?"
intQ = MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes?")

If intQ = vbYes Then
ActiveWorkbook.Save
End If
Application.Quit
End Sub
0
 

Author Comment

by:Frank Freese
ID: 34875496
Perfect! Thank you - I was close (almost)
0
 

Author Closing Comment

by:Frank Freese
ID: 34875501
great job - thank you
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34875534
You're welcome and thanks for the grade.

jppinto
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question