Can code in an Add-In evaluate every workbook opened?

  • Many of my users are sporadic about making back-up copies of their workbook files. These are workbooks based on a template that I built, and all use an add-in that I created and keep up-to-date. I'm trying to think of ways to "help them" make back-ups.

    It's too late to put code in This Workbook for the exisiting files (but the template has been updated for new files). But I wondered if I jcould put code in the add-in that would do this? The code would have to evaluate every Excel file the user opened; if a certain named range was in the workbook, then I could simply use SaveCopyAs to make a back-up and write the date or some other "code word" into the file so it only gets done once on any given day.

    If this were on my own computer, I know I could put code using the Application object in my Personal macro workbook. And I did see Chip Pearson's page on doing things like that. But I couldn't see if it could be adapted for use in an add-in.

    Has anyone else done this?

  • Re: Can code in an Add-In evaluate every workbook opened?

    To summarise CPs page (This is taken from something I knocked up myself)

    Add the following to the ThisWorkBook class module

    Or have a look at the attached - that is an XLSM file rather than an add-in, but the principal is the same.

    This uses a variable (oXL) declared as WithEvents. This caused Application events to be mirrored to the Workbook class. You can then do what you want. There's sample code (a fancy term for a couple of MegBox's) that execute when a Workbook is loaded or a new blank workbook opened. If you were to use this then I'd suggest the WorkBook_BeforeSave event would be a good one to use to track which workbooks are saved. After all, workbooks might be opened, but closed again without saving...

  • Re: Can code in an Add-In evaluate every workbook opened?

    Thanks, cytop. I tried something like this last night, but as I mentioned it would only fire for workbooks openeing as the add-in was initially loading. Once the add-in was opened, the code didn't fire for any other workbook that opened up.

    I'll give it another shot in a little bit and we'll see what happens.

  • Re: Can code in an Add-In evaluate every workbook opened?

    Sorry for the delay. Couldn't download the file so I created an add-in and manually added the code. Works great!! Thank you!!


Participate now!

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