capturing open, close, and exit events

  • I'm trying to set up excel so that I get notified (e.g. a message pops up) whenever I start it, open a file, close a file, or exit the application.
    I'm trying to do something similar to AutoNew(), AutoOpen(), AutoClose(), and AutoExit() from Word VBA.


    So far, I've figured out how to get a message when I start excel. I put the following code inside of a personal.xls file in my XLStart directory.


    Code
    Private Sub Workbook_Open()
        MsgBox "Workbook Opened!"
    End Sub


    However, I can't seem to get it to notify me when I open a file, close a file, or exit the application.


    Does anyone know how to do any of those?


    thanks.

  • Re: capturing open, close, and exit events


    Quote from chrisxs5

    Just curious why you need to do this?


    I want Excel to automatically create a log of when I used it and what files I used.

  • Re: capturing open, close, and exit events


    Sounds like it would be better if you date stamped a cell versus giving yourself a message. Do you want a continuing log or just to know the last time you logged in? Also, will this be for all workbooks, sheets you open or just certain workbooks?

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: capturing open, close, and exit events


    Quote from chrisxs5

    Sounds like it would be better if you date stamped a cell versus giving yourself a message. Do you want a continuing log or just to know the last time you logged in? Also, will this be for all workbooks, sheets you open or just certain workbooks?


    I want a continuing log for all workbooks.


    So for example if this thing were running, then I'd be able to look in the log file to find out which Workbooks I had open last Thursday at 2pm or how many hours I had Excel open for last Tuesday.


    I've already worked out VBA code for writing to a log, I just need to figure out how to get notified when I open a workbook, close a workbook, and exit Excel.

  • Re: capturing open, close, and exit events


    When I paste your msg into the thisworkbook page it runs just fine. Why dont you past the msg after the could that logs the open. That way when you see the message you know the log worked as well.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: capturing open, close, and exit events


    Quote from chrisxs5

    When I paste your msg into the thisworkbook page it runs just fine. Why dont you past the msg after the could that logs the open. That way when you see the message you know the log worked as well.


    I know that the Workbook_Open code runs fine when the user starts Excel.


    If I put that code inside a thisworkbook page, then I suppose I'd be notified whenever I opened that particular workbook, but I don't think it would notify me whenever I opened any workbook.


    Update: I've gotten Excel to notify me whenever it exits by putting the Workbook_BeforeClose into an Add-In. I still haven't figured out how to get notified on regular workbook opens and closes yet though.

  • Re: capturing open, close, and exit events


    AN add-in was my next suggestion. Put the open in the add-in as well.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!