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?

--
Thanks+Bye,
Christoph

Rio Riay Riayo - Gordon Sumner, 1979