Consulting

Results 1 to 11 of 11

Thread: Solved: Stop pop-up when excel closes

  1. #1
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    Solved: Stop pop-up when excel closes

    I did a save on all my workbooks using a loop and when I did Application.Quit at the end of the code, it popped up the msg asking me if I want to save my workbook. How do u get excel to ignore that msg. I've done it b4 but its been a while back. And now I keep everything in case i need it in the future.

    Thanx

  2. #2
    VBAX Regular
    Joined
    Jun 2004
    Location
    Cordova, Alaska
    Posts
    10
    Location
    [VBA]Application.DisplayAlerts = False[/VBA]
    Will prevent the message from appearing.
    Conversely, you just use [VBA]Application.DisplayAlerts = True[/VBA] to re-enable the alert messages.

    This help?
    Dan

  3. #3
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    Yep

    Thank You again Dan!

  4. #4
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Daxton,
    Just as an addition to the above posts. The DisplayAlerts causes the message to not appear, which causes Excel to automatically save the file. If that's what you want then everything should be OK, if you don't want to save the file, then additional code will be required to disable the save action.
    The most difficult errors to resolve are the one's you know you didn't make.


  5. #5
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    ?????

    What's the additional code? And thank you for responding w/this CBrine.

  6. #6
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Daxton,
    I may have been mistaken. I was thinking the activeworkbook.save, or activeworkbook.close commands would default to save. The application.quit seems to bypass the save when used with the displayalerts =false. I did some tests, and got some strange results though? When I programmed a button with the displayalerts =false and the application.quit, and ran it, it still prompted me for the save, but if i stepped through it, it didn't prompt for the save?(Only when something changed on the workbook).
    Did your's do this? Anyone have any idea why this would be happening?
    The most difficult errors to resolve are the one's you know you didn't make.


  7. #7
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    Hey

    Mine did do this so I created the code:

    [VBA]
    Sub endModule()

    For i = 1 To Workbooks.Count
    Workbooks(i).Save
    Next i

    Application.Quit
    End Sub

    [/VBA]

    And it works fine now.

  8. #8
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Daxton,
    I did something close to yours. I set the saved flag to true, then excel will not prompt you to save and the application.quit will bypass the save. So if you want to not save the activeworkbook, you might want to try this. It will save each open workbook, except for the workbook that the code is executed on, prior to closing excel.

    [VBA]
    Private Sub CommandButton1_Click()
    Dim wb As Workbook, wb2 As Workbook
    Set wb = ActiveWorkbook
    For Each wb2 In Application.Workbooks
    If wb2.Name = wb.Name Then
    wb2.Saved = True
    Else
    wb2.Save
    End If
    Next
    Application.Quit
    End Sub
    [/VBA]
    The most difficult errors to resolve are the one's you know you didn't make.


  9. #9
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I have a workbook that gives users access to info and they can then print it out but when they close the book I don't want them to save any changes. I used this code from a button created from the control toolbox:

    [VBA]
    Private Sub CommandButton1_Click()
    Application.DisplayAlerts = False
    Application.Quit
    End Sub
    [/VBA]
    It just closes the program, no questions asked.

    If you want to save the changes and close the book with no questions asked, try this:

    [VBA] Option Explicit
    Private Sub CommandButton1_Click()
    Dim i As Variant
    For i = 1 To Workbooks.Count
    Workbooks(i).Save
    Next i

    Application.DisplayAlerts = False
    Application.Quit
    End Sub
    [/VBA]
    Steve

  10. #10
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi, just an observation for others who may be reading this thread. You need to be careful when turning off alerts as this may have undesirable affects depending upon what you are doing. The alerts are there for a reason so if you disable them then errors/alerts which you may wish to be advised of will not occur.

    If Steve had used this code instead then this only closes the workbook and keeps Excel running so you run the risk of operating without the alerts.

    [VBA] Application.DisplayAlerts = False
    ThisWorkbook.Close
    [/VBA]

    Therefore you should turn alerts back on when youve finished your action. However, whenever you open Excel the alerts are 'reset' to True regardless whether they were False when you closed Excel. So Steves code is fine because he is closing the Application itself so things will return to normal. Sorry for butting in but I thought I should mention this.

  11. #11
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Picking up on Parry's fine post, with the workbook close code, you can specify whether or not you want to save that workbook with a true or a false following on the same line. This example would save the workbook on closing...

    [vba]ThisWorkbook.Close True[/vba]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •