Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro to run on closure (2002)

    Hi,

    I have an excel spreadsheet that needs to have a macro run each time the user closes the workbook. Normally I put a command button on for them to use but there are over 700 people who will have their own version of this spreadsheet and not all are terribly computer literate to say speak the English Language. My concern is that if they forget to click the command button when they exit the spreadsheet, the sheet will not be formatted in the manner it needs to be. Is there a way to create a module that will run the code each time the workbook is closed.

    Thanks,
    Leesha

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to run on closure (2002)

    The usual way is to hook the code into the BeforeClose (and perhapsBeforeSave) events in the ThisWorkbook module. To code these events, in the VBE module explorer select the ThisWorkbook Module, and then in the upper left dropdown of the code window select WorkBook, and in the upper right dropdown (Declarations) select BeforeClose; an empty Sub "Private Sub Workbook_BeforeClose(Cancel As Boolean)" is created for you.

    Search this forum for the keywords "ThisWorkbook" and "BeforeClose" for more on coding these Events, post back if you need more assistance.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to run on closure (2002)

    Thanks John, this worked beautifully!

    Leesha

Posting Permissions

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