05.10.2004 12:35, Bob Phillips schrieb:

Hi Bob,

Use application events.

Firstly, all of this code goes in the designated workbook, workbook 1.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
With Wb.ActiveSheet
.PageSetup.LeftFooter = "some text"
End With
End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
Set gwbsNewSheet = sh
End Sub


Thanks for your reply, points me into the right direction
your sample code works. Also I found out, that i can do it all inside the
ThisWorkBook-Module like this (not using event-Classes)

Option Explicit

Dim WithEvents gThisApp As Application
Dim gwkbNew As Workbook
Dim gwksNew As Worksheet

Private Sub Workbook_Open()
Dim wks As Worksheet
Set gThisApp = Application
gThisApp.Workbooks.Open "C:\DATA\x.xls"
gwkbNew.Worksheets.Add
Set wks = gwksNew
wks.Name = "newnewnew"
End Sub

Private Sub gThisApp_WorkbookOpen(ByVal Wb As Workbook)
Set gwkbNew = Wb
End Sub

Private Sub gThisApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
If TypeOf Sh Is Worksheet And Wb Is gwkbNew Then
Set gwksNew = Sh
End If
End Sub


btw is there also an event if you create a new sheet with
someWorkSheet.Copy
NewSheet seems to fire after 'worksheets.Add' (but not .Copy)?


--
Gruesse, Christoph

Rio Riay Riayo - Gordon Sumner, 1979