|
[ Pobierz całość w formacie PDF ]
.Its value is eitherTrue(pressed) or False(not pressed).You can often use a ToggleButton in placeof a CheckBox control.&' &' &' 45 539671 ch38.qxd 8/28/03 10:06 AM Page 745C H A P T E R3838Working withExcel Events&' &' &' &'In This ChapterUnderstanding eventsn the preceding chapters, you ve seen examples of VBAIevent handler procedures.These procedures are the keysUsing workbook-levelto making your Excel applications interactive.This chaptereventsprovides an introduction to the concept of Excel events andincludes many examples that you can adapt to meet your ownWorking withneeds.worksheet eventsUsing non-objecteventsUnderstanding Events&' &' &' &'Excel is capable of monitoring a wide variety of events andexecuting your VBA code when a particular event occurs.Thischapter covers the following types of events.&' Workbook events: These occur for a particularworkbook.Examples include Open (the workbook isopened or created), BeforeSave (the workbook isaboutto be saved), and NewSheet (a new sheet isadded).VBA code for workbook events must be storedin theThisWorkbook code module.&' Worksheet events: These occur for a particular work-sheet.Examples include Change (a cell on the sheet ischanged), SelectionChange (the cell pointer ismoved), and Calculate (the worksheet is recalculated).VBA code for worksheet events must be stored in thecode module for the worksheet (for example, the mod-ule named Sheet1).&' Events not associated with objects: The final categoryconsists of two useful application-level events: OnTimeand OnKey.These work differently from other events. 45 539671 ch38.qxd 8/28/03 10:06 AM Page 746Part VI &' Programming Excel with VBA746Entering event handler VBA codeEvery event handler procedure must go into a specific type of code module.Codefor workbook-level events goes into the ThisWorkbook code module.Code for work-sheet-level events goes into the code module for the particular sheet (for example,the code module named Sheet1).In addition, every event handler procedure has a predetermined name.You candeclare the procedure by typing it, but a much better approach is to let the VBE doit for you.Figure 38-1 shows the code module for the ThisWorkbookobject.Toinsert a procedure declaration, select Workbookfrom the objects list on the left.Then select the event from the procedures list on the right.When you do this, youget a procedure shell that contains the procedure declaration line and an EndSubstatement.Figure 38-1: The best way to create an eventprocedure is to let the VBE do it for you.For example, if you select Workbookfrom the objects list and Openfrom the proce-dures list, the VBE inserts the following (empty) procedure:Private Sub Workbook_Open()End SubYour code goes between these two lines.Some event handler procedures contain an argument list.For example, you mayneed to create an event handler procedure to monitor the SheetActivateeventfor a workbook.If you use the technique described in the previous section, the VBEcreates the following procedure:Private Sub Workbook_SheetActivate(ByVal Sh As Object)End SubThis procedure uses one argument (Sh), which represents the activated sheet.Inthis case, Shis declared as an Objectdata type rather than a Worksheetdata typebecause the activated sheet also can be a chart sheet.Your code can, of course, make use of data passed as an argument.The followingexample displays the name of the activated sheet by accessing the argument s Nameproperty.The argument becomes either a Worksheetobject or a Chartobject. 45 539671 ch38.qxd 8/28/03 10:06 AM Page 747Chapter 38 &' Working with Excel Events747Private Sub Workbook_SheetActivate(ByVal Sh As Object)MsgBox Sh.Name & was activated. End SubSeveral event handler procedures use a Booleanargument named Cancel.Forexample, the declaration for a workbook s BeforePrintevent isPrivate Sub Workbook_BeforePrint(Cancel As Boolean)The value of Cancelpassed to the procedure is False.However, your code can setCancelto True, which cancels the printing.The following example demonstrates this:Private Sub Workbook_BeforePrint(Cancel As Boolean)Msg = Have you loaded the 5164 label stock? Ans = MsgBox(Msg, vbYesNo, About to print. )If Ans = vbNo Then Cancel = TrueEnd SubThe Workbook_BeforePrintprocedure executes before the workbook prints.Thisprocedure displays a message box asking the user to verify that the correct labelstock is loaded.If the user clicks the No button, Cancelis set to True, and nothingprints.Using Workbook-Level EventsWorkbook-level events occur for a particular workbook.Table 38-1 lists the workbookevents, along with a brief description of each.Keep in mind that workbook eventhandler procedures must be stored in the code module for the ThisWorkbook object.Table 38-1Workbook EventsEvent Action That Triggers the EventActivate The workbook is activated.AddinInstall The workbook is installed as an add-in.AddinUninstall The workbook is uninstalled as an add-in.AfterXmlExport An XML file has been exported (Excel 2003 only).AfterXmlImport An XML file has been imported (Excel 2003 only).BeforeClose The workbook is about to be closed.BeforePrint The workbook (or anything in it) is about to beprinted.Continued 45 539671 ch38
[ Pobierz całość w formacie PDF ]
zanotowane.pldoc.pisz.plpdf.pisz.plhanula1950.keep.pl
|