Solved

Macro to Close Excel

Posted on 2011-02-11
10
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

752 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