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

Thread: Help! 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

    Hi everyone,

    I have some code that SHOULD ask the user to save the workbook with the beforeclose event. The message does come up, but the problem is, if the user selects "Yes" on the msgbox, the code saves, but right after that, excel asks with ITS message if the user wants to save the changes. I dont want the 2nd (excel's) save message to come up. Here is my code:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set DataSheet = Worksheets("DataSheet")

    opensheet = ""
    'Call TestOpenSheets
    Application.Run "Navigation.TestOpenSheets"

    If Left(ActiveWorkbook.Name, 6) = "Retail" Then

    'code

    Else
    'code

    If Left(CurrFile, 6) = "Retail" Then Exit Sub

    Application.ScreenUpdating = False

    CurrFile = ActiveWorkbook.Name

    'code

    Application.DisplayAlerts = False
    DataSheet.Delete
    Worksheets("Navigation").Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Disables Counted Shutdown Before closing
    Call Disable
    ActiveWorkbook.Save

    Else

    Application.DisplayAlerts = False
    DataSheet.Delete
    Worksheets("Navigation").Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Disables Counted Shutdown Before closing
    Call Disable
    ActiveWorkbook.Saved = True
    End If

    ActiveWorkbook.Saved = True
    End If

    End Sub


    Why is the message coming up? i am telling excel the workbook IS saved, yet it still asks??

    Can i accomplish this with application.displayalerts=false? If i use this, does the file save or NOT save when overriding? also, do i have to turn alerts back on or do they automatically come back on? (for example if i have more than one file open and i want alerts ON in that one)

    Thanks

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try inserting this at the end of your code, and/or at the end of your If blocks, depending on when you want your code to handle the actual save process:

    Cancel = True


  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why would you want to bypass Excel's built in Save As? I'm smelling moronic boss or equally stupid client on this one.

  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

    Mark, lol, actually the reason im doing it is im using code TStom gave me to export information to a text file on the close event. but i want to make sure the user WANTS to save the changes. if i dont ask the question, the export will occur, then save regardless of if the user clicks yes or no to excel's save question becuase the information will export to a text file. If i ask the question, i can control the export.

    But i guess it does boil down to a boss, the reason im exporting is because he wants to run reports like in access, but i dont know access, so im using text files to do it

    tom, i will try your idea, i dont have the file with me. But can you answer my other questions? if i disablealerts before the save, and then DONT re-enable, will it prevent other open workbooks from displaying alerts or is the displayalerts method only applicable to the workbook its ran from?

    Thanks

    [ This Message was edited by: robfo0 on 2002-04-24 23:16 ]

  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

    Tom, how do I use the cancel method?

    I'm trying a sample with this code:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    savetest = MsgBox("save?", vbYesNo)

    If savetest = vbYes Then
    ActiveWorkbook.Save

    Else
    End If
    ActiveWorkbook.Saved = True
    Cancel = True

    End Sub


    This prevents the message from coming up, but it also prevents the file from closing at all!...am i doing something wrong?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ho Rob
    Application.DisplayAlerts applies to the entire Excel application. Resets to deault(False) if the app is closed and then re-opened...
    Also in the:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    event...
    If Cancel = True then you are cancelling the Close Event.
    The workbook will cannot close.
    If the user does not want to save the workbook, then close with this line.
    Activeworkbook.Close SaveChanges: = False
    Hope this helps.
    I am not exactly clear on what you are doing???
    Tom

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

    Default

    TSTom, maybe you can help me. What i am doing is using the before_close event to export my data you helped me with to a text file. (i made this process automactic, it imports it on open, and exports on before close).

    If for example, excels save dialog came up when i closed the file, but i did not want to save my changes, i would click no, BUT, since my text export is the before_close, it would still export the information even if i dindt want to save it, thus saving it anyway.

    This is what i am trying to prevent using vbyesno msgbox. if the user clicks yes, it proceeds with the text export, and saves. if they click no, it does not export and i use activeworkbook.saved=true. I have tried simple examples like this:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    savetest = MsgBox("save?", vbYesNo + vbQuestion, "Save " & ActiveWorkbook.Name)

    If savetest = vbYes Then
    ActiveWorkbook.Save

    Else
    End If
    ActiveWorkbook.Saved = True

    End Sub

    and it DOES what i want it to do, but in the code of my actual workbook (which resembles the code i first posted in this string), it does NOT work. It will ask the question, but then it ALSO asks again if i want to save, which i would like to prevent. I'm really not sure why it is doing this, i have tried putting a few activeworkbook.saved=true in a few places, but it still does it. after that last activeworkbook.saved=true, there are NO other changes, so why does it still ask the question? any ideas?



    Thanks

    [ This Message was edited by: robfo0 on 2002-04-25 01:43 ]

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
  •