Personal Macro Workbook question

[jayy]

Hi,

Does anyone know if it's possible to have a Personal Macro Workbook do certain steps whenever a file is opened or closed? For example, if I opened a file, I'd like the Personal Macro workbook to automatically start up and do some stuff. Then when I close the file, I'd like the Personal Macro Workbook to again do some more stuff, save the file and then close it. I'd like this to happen without the user having to click any buttons or keystrokes.

I experimented with Auto_Open and Auto_Close, but since that's tied to the Personal Macro Workbook it only works when the Excel program is either started up or shut down.

Thanks for looking at my questions.

Jay

[RobynC]

In the workbook that you want the actions to be performed on write a Private Sub workbook_open() event, in the ThisWorkbook code section. the macro will then fire whenever that workbook is opened.

Hope that helps

[jayy]

Hi RobynC,

Thanks very much for your reply!

I ended up figuring this out just a few seconds ago. I'm actually wanting this to work on all files, including files that don't have macros in them. So the solution is to use a Personal Macro Workbook. I stumbled upon this helpful web page "http://www.cpearson.com/excel/Events.aspx" which showed me the steps of creating a class module with application events. By calling this class with the auto_open sub in the Personal Macro Workbook I'm now able to do things whenever files are opened or closed. Very cool stuff!!

Again, thanks for taking the time to reply. I appreciate it!

Jay