Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Stop the save message!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, a problem: I have a macro which goes through my workbook, and deletes some sheets, and deletes a module in my workbook. This all works fine. But, in my before close event, i have a piece of code which saves the workbook, thus preventing the Save Question box from coming up (well, supposed to). The problem is, when i run my code which deletes the module and sheets, and then close the workbook, the save question STILL comes up! (if i do NOT run the code which deletes, there is NO save message). The big problem here is that IF the user for some reason clicks "no" to the save question, it corrupts the excel file, and i can no longer open it!

    I do not want to use the code "application.displayalerts=false" because there may be other workbooks open which i DO need alerts.

    Can anyone think of any solutions? I would post my code, but theres a ton of it. let me know if you really need it, thanks.

  2. #2
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do the application.displayalerts=false before you save your file, then after you save it, do application.displayalerts=true.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    well thats the problem, the save IS working, i can see it save, but then it asks again on the before close event. and i cant put the displayalerts=false in there because then there wouldnt be a way to turn it back on...

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    another update: I tried playing around with the code just to make it work. I tried putting "application.displayalerts=false" in the workbook_open, and every module that runs, and even in the before close event, but the message asking if i want to save STILL comes up!!?! make it stop! does deleting code and sheets require the save question to come up?? if the user clicks no, it causes fatal errors! Help!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, after hours of toiling, i finally found the problem. It was in the deleting of the sheets on close. I am now calling form the before_close event the macro "DeleteSheets". I tried deleting both sheets from there, but i was having the same problem. BUT i finally noticed taht if i only delete one sheet per macro, the save message did NOT come up. IT works! But my question now is: does anyone know why it DOES work? whats the difference of having the delete sheets in 2 macros as opposed to one?

    Thanks

    Sub DeleteSheets()
    Application.DisplayAlerts = False
    Sheets("Navigation").Delete
    Application.DisplayAlerts = True
    Call DeleteDataSheet
    End Sub

    Sub DeleteDataSheet()
    Application.DisplayAlerts = False
    Sheets("DataSheet").Delete
    Application.DisplayAlerts = True
    End Sub

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could put this in the workbook before close event

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    If ThisWorkbook.Saved = True Then Cancel = False

    End Sub

    and the macro
    Sub DeleteSheets()
    Application.DisplayAlerts = False
    Sheets("Navigation").Delete
    Sheets("DataSheet").Delete
    Application.DisplayAlerts = True
    activeworkbook.save
    Workbooks("Book1").Close
    End Sub

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    were you putting the displayalerts=true before the second file was deleted?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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