Catching NewSheet-Event in another WorkBook

[Christoph Basedau]

Hi

I have the following concept:
Workbook-1 is opened (by simply doubleclicking the xls-file)
and executes Macro/VBA-Code.
This code opens another xls-file as a new workbook.
then the code of wb-1 creates some new WorkSheets (with wb2.WorkSheets.Add)
in wb-2, renames them and fills in some formula and data.

I thought the best way to get a reference to a newly created sheet,
is to 'listen' to the NewSheet-Event of wb-2. Using the String Index
of the WorkSheets-Collection:
Set newWks = wb2.WorkSheets("old name (2)")
seems a little fragile to, although it works for the moment.
After all it's not that easy to get the NewSheet-event

I tried (code in wb1):

Dim WithEvents wb2 as WorkBook
Dim gwbsNewSheet as WorkSheets 'global var for newsheet
'...
Public Sub wb2_NewSheet( sh as Object)
Set gwbsNewSheet = sh
End Sub

But this didn't work, the event seems not be raised/recognized.
Alternatively i used also another Application-Object and then
listened to:

Dim App2 as new Application
Dim wb2 as Workbook
set wb2 = App2.WorkBooks.Open(...)

Public Sub App2_WorkBookNewSheet(wb as WorkBook, sh as Object)
Set gwbsNewSheet = sh
End Sub

This works, but our Project-Master don't wants this technique because
it uses DCOM/RPC which is subject of change (see XP-SP2) if
understood him correctly.

So the question: How to catch the NewSheet-Event in the 2nd wb?

 

[keepITcool]

you'll need to set up events for the application.

to create this you can create your own class module,
but you can also easily do it in ThisWorkbook.

use the dropdown in the top of the module screen to select
the events available for xlApp.

Option Explicit
Dim WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()
'instantiate the xlapp variable..
Set xlApp = Application
End Sub

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox Wb.Name & " created..!"
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Bob Phillips]

Hi Christoph,

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

 

[Christoph Basedau]

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)?