Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Excel is programmed to monitor many different events that occur. These events can be classified as the following:

• Workbook events: Events that occur for a particular workbook. Examples of such events include Open (the workbook is opened or created), BeforeSave (the workbook is about to be saved), and NewSheet (a new sheet is added).

• Worksheet events: Events that occur for a particular worksheet. Examples include Change (a cell on the sheet is changed), SelectionChange (the user moves the cell indicator), and Calculate (the worksheet is recalculated).

• Chart events: Events that occur for a particular chart. These events include Select (an object in the chart is selected) and SeriesChange (a value of a data point in a series is changed). To monitor events for an embedded chart, you use a class module, as I demonstrate in Chapter 18.

• Application events: Events that occur for the application (Excel). Examples include NewWorkbook (a new workbook is created), WorkbookBeforeClose (any workbook is about to be closed), and SheetChange (a cell in any open workbook is altered ). To monitor Application -level events, you need to use a class module.

• UserForm events: Events that occur for a particular UserForm or an object contained on the UserForm. For example, a UserForm has an Initialize event (occurs before the UserForm is displayed), and a CommandButton on a UserForm has a Click event (occurs when the button is clicked).

• Events not associated with objects: The final category consists of two useful Application -level events that I call On events: OnTime and OnKey . These work in a different manner than other events.

This chapter is organized according to the preceding list. Within each section, I provide examples to demonstrate some of the events.