Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Need a workbook BeforeClose event

    How do I add a macro in a workbook that prompts the user with a Save/Not Save option when the user tries to close the file?

    I'm not expecting a test to determine if the workbook has been saved recently (though that could be an option), but a simple bit of code to ensure that a user has been asked to save or not and then if yes, to save the ActiveWorkbook. The code should be able to overwrite a file with the same name without interrupting the code or the user.
    Last edited by Arcturus16a; 2013-03-11 at 14:57.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Saved = False

    End Sub

    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Zeddy.

    So is ActiveWorkbook.Saved = False is "turning off" the switch that recognizes that Excel has saved the file since the last change was made?

    If that's true then just opening and closing the file without changes would cause the Save prompt to pop up, wouldn't it? That's not happening here.
    Last edited by Arcturus16a; 2013-03-11 at 18:57.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Arcturus,

    The code that Zeddy had posted is correct. By setting the property to false, Excel will not see the file as saved nor supress the prompt to save it each time. I have tested it to make sure and I am prompted to save on closing immediately after opening the workbook and making no changes. Make sure you have it in the Workbook_BeforeClose event routine in the Workbook module. See image

    HTH,
    Maud

    Save.jpg

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Maudibe. I got it now. I had placed the code in a module. I've never used "ThisWorkbook". What's it for? Why the difference between it and a Module?

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Arcturus

    Because your post was labelled "Workbook Before Close Event" I assumed you knew about Private Sub Workbook_BeforeClose(Cancel As Boolean), as this IS the "Workbook Before Close Event"
    Forgive me for giving such a short, blunt, reply in my post#2.

    "ThisWorkbook", which you will find in the vba project explorer pane (press Ctrl-R in vba editor to display the vba project explorer pane) introduces you to the "Event" routines which you can write code for.

    Do a search for Excel Event routines, to open up a whole new exciting world of things you can do with excel vba.

    zeddy

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A quick general response:
    Modules are for general VBA code and functions. They are either "called" by code or as function
    The other modules (thisworkbook and worksheets) or for events (open, close, change, etc) that occur during the use of the workbook
    thisWorkbook is for workbook level events (events happening to workbook or to all worksheets)
    Worksheets are for codes on events on those individual worksheets

    See Chip Pearson's site (http://www.cpearson.com/excel/Events.aspx) for a nice overview of events

    Steve

Posting Permissions

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