Info Lov

Any of the events listed can by captured, just as workbook and worksheet events were captured in an earlier chapter. The following example uses the NewWorkbook event to automatically set up footer information. This code is placed in the class module, below the xlApp declaration line you just added:

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) Dim wks As Worksheet

With Wb

For Each wks In .Worksheets wks.PageSetup.LeftFooter = wks.PageSetup.RightFooter = Next wks End With

'Created by: Now

" & .Application.UserName

End Sub

The procedure placed in a class module does not run automatically as events in workbook or worksheet modules would. An instance of the class module must be created and the Application object assigned to the xlApp property. After that is complete, the TrapAppEvent procedure needs to run. As long as the procedure is running, the footer will be created on each sheet every time a new workbook is added (see Figure 20.3):

Public myAppEvent As New clsAppEvents Sub TrapAppEvent() Set myAppEvent.xlApp = Application End Sub

Figure 20.3

Use an application event to automate the creation of a footer in each new workbook.

Figure 20.3

Use an application event to automate the creation of a footer in each new workbook.

0 0

Post a comment