Microsoft Office Tutorials and References
In Depth Information
Event Procedure Examples
AnimationInProgress = True
StartVal = Range(“StartRow”)
For r = StartVal To 748 - Range(“NumDays”)
Range(“StartRow”) = Range(“StartRow”) + 1
Next r
AnimationInProgress = False
End Sub
This macro uses a Public variable, AnimationInProgress , to keep track of
whether the chart is being animated. If so, running the macro sets the variable to
False, and the procedure ends. This allows a single button to both start and stop the
Event Procedure Examples
It’s possible to write macros that respond to certain events. For example, opening a
workbook is an event, and you can write a macro (called an event procedure ) that is
executed when that event occurs. For an example of a Workbook_Open event
procedure, see “Protecting all charts when the workbook is opened,” earlier in this chapter.
Worksheet_Change event procedures
The examples in this section deal with the Worksheet_Change event, which is one
of many events available at the worksheet level. This event is triggered whenever a
cell is changed in a worksheet. As you’ll see, these types of macros can be very
useful to update charts automatically.
The event macros in this section must be located in the code module for the
worksheet (for example, the module named Sheet1). They will not work if
they are contained in a standard VBA module.
When you activate a code module for a chart, sheet, or workbook, you can
use the drop-down lists at the top to select an object and event. Figure
16-16 shows a code module for a worksheet. The object (Worksheet) is
selected in the left drop-down list, and the right drop-down list displays all
the events that can be monitored for the object. When you choose an item
from the list, the procedure “shell” (consisting of the first and last statements)
is inserted for you.
Search JabSto ::

Custom Search