Programming Excel VBA: Event Triggered Programs

Excel VBA is capable of delivering event triggered programs.  There are many different events that a developer can specify that will trigger a program:  opening a workbook, activating a sheet, saving a workbook, closing a workbook, day of the week, etc.  Writing and using event triggered programs can save much time an energy.  The objective of this post is to write an event driven program that opens up other related workbooks.  In my experience I have found that several related Excel workbooks are often opened up together to get a task accomplished.  For example a bookkeeper may want to open up the general ledger, income statement, balance sheet, and cash flow statement every month to generate the next set of financial records.  This repetitive task can involve digging through multiple files/folders.  One can quickly and easily program Excel to complete this tasks.  Obviously the possibilities are endless. One can use event triggered programs to:  print after closing a file, update certain fields on given days, format before printing, etc.

OPEN EXCEL VBA AND SPECIFYING THE EVENT

Open up Excel VBA by pressing Alt+F11.  In the drop down window select “Workbook” and “Open” to create a program that runs one the file is opened.

Note that you must write the Excel VBA program in a module under the file you want this program to appear.

WRITING THE PROGRAM

It is often good practice to make sure you inform/ask the user of the program that is about to be run.  You might now want to open up a whole collection of financial spreadsheets, you may just want to take a peak at a particular transaction or report.  This can be accomplished by writing the program with an If-then statement where the condition tested is selected by the user through a message box.

If the user selects “yes” then the following code runs…


This is a quick and easy program that can be built upon to automate many of the repetitive tasks analyst working on Excel.


Advertisements

One thought on “Programming Excel VBA: Event Triggered Programs

  1. I have to thank you for the efforts you’ve put in writing this website. I really hope to view the same high-grade blog posts by you later on as well. In fact, your creative writing abilities has inspired me to get my very own site now 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s