MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Chigaru    Date: Feb 12    Category: MS Office    Views: 1718

I have an Auto_Close(macro) subroutine where I do the ActiveWorkbook.Close(0)
Which of course will close the Excel file without saving it and NOT prompting
the user whether to save the Excel file.

However, while I am editing and debugging the Excel file which has the the
Auto_Close subroutine(macro) in it.
And when I manually "Saved" the Excel via the File command bar button and select
"Exit" from the list.
Upon "Exiting" the Excel file, Excel prompts the user whether to save the Excel
file, even though I already saved it.
How can I totally eliminate this annoying prompting panel to appear, when I
already have Close(0) or "Saved" the Excel file ?
Sometimes it even promts the person working with the Excel file, the workbook
password upon "Exiting" when an error has occured in it.

There must be some sort 1 line of code that would solve this and not display
this prompt and "Exit" the Excel file.



6 Answers Found

Answer #1    Answered By: Amy Brown     Answered On: Feb 12

I found the answer.
The one line  of code  is simply "Application.Quit"

Answer #2    Answered By: Mandy Harvey     Answered On: Feb 12

You realise that that shuts down all of Excel, not just a workbook?
check out
Application.DisplayAlerts = False
and the Saved property, eg.:
ActiveWorkbook.Saved = True

Answer #3    Answered By: Kawthar Malik     Answered On: Feb 12

I found the answer.
The one line  of code  is simply "Application.Quit"

Answer #4    Answered By: Komal Malik     Answered On: Feb 12

I have the [ Application.DisplayAlerts = False ] statement right before the [
ActiveWorkbook.Close(0) ] statement
For some reason, it didn't prevent the prompted msg asking the user  if file
needed to be saved.

What is the purpose of the ( ActiveWorkbook.Saved = True ) statement ?

If the Excel file  is already saved or doesn't need to saved.
The ActiveWork.Close(0) closes the workbook, however the prompted msg asking if
Excel file needs to be saved is displayed...( sigh )

Answer #5    Answered By: Marsha Miller     Answered On: Feb 12

I think the problem arises because (I quote) "An Auto_Close procedure
runs just before the workbook containing the procedure closes."

So the auto_close procedure shouldn't itself need to close  the
workbook as it's already in the process of being closed.

Two answers:
1. Don't close the workbook from within the auto_close procedure -
it's there to tidy up just before the workbook is closed by
something/one else. I guess in your case the prompt  you're getting is
coming up before Auto_Close is called.

2. If you call this procedure from within VBA and you want it to close
the workbook then call it something else, eg. Sub myClose.

ActiveWorkbook.Saved is a property which is True if no changes have
been made to the workbook since it was last saved; False otherwise.
But you can set this property to True if you want to close a modified
workbook without either saving  it or being prompted to save  it.

Set DisplayAlerts property to False if you don't want to be disturbed
by prompts and alert messages while a macro is running; any time a
message requires a response, Microsoft Excel chooses the default
response. You shouldn't need to use this if you've used the .Saved
property set to True.

Answer #6    Answered By: Judy Gray     Answered On: Feb 12

I removed the "Application.Quit" in the Auto_Close subroutine.
I coded it as follows:
Application.DisplayAlerts = False
ActiveWorkbook.Saved = True
ActiveWorkbook.Close( 0 )

Now, there are no prompts when exiting the Excel file  and all of Excel is not
I think the above is safer and better than having the Application.Quit
I am not sure how one would use the Application.Quit

Didn't find what you were looking for? Find more on Auto_Close Or get search suggestion and latest updates.

Related Topics:



Related Post