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…