adding code to worksheets made at runtime

[Amedee Van Gasse]

(Excel 2000)

I'm working on an Excel VBA application.
One of the things it does, is generating a worksheet and an pivot table at
runtime.
There is certain code that has to be executed on every change of the pivot
table so I use the PivotTableUpdate event handler. However, this code has
to be placed in the worksheet in order to work, and since the worksheet is
generated... problem!
Another problem is that when new sheets, based on the sheet with the pivot
table, are made using ShowPages PageField:=..., the VBA code should also be
present in the new sheets. But of course every reference to the pivot table
inside the code has to be changed because Excel adds a serial number after
the name of the pivot table.

Am I searching in the right direction or is there a much simpler solution?

--
Amedee Van Gasse

To top-post is human, to bottom-post and snip is sublime.

Dit bericht is geplaatst in een nieuwsgroep. Post je evt antwoord of
vervolgvraag graag in dezelfde thread in de nieuwsgroep a.u.b.

[Amedee Van Gasse]

Amedee Van Gasse schreef in
<news:Xns95218ACEDB1Eam...@194.109.133.29>:

> (Excel 2000)
>
> I'm working on an Excel VBA application.
> One of the things it does, is generating a worksheet and an pivot
> table at runtime.
> There is certain code that has to be executed on every change of the
> pivot table so I use the PivotTableUpdate event handler. However, this
> code has to be placed in the worksheet in order to work, and since the
> worksheet is generated... problem!
> Another problem is that when new sheets, based on the sheet with the
> pivot table, are made using ShowPages PageField:=..., the VBA code
> should also be present in the new sheets. But of course every
> reference to the pivot table inside the code has to be changed because
> Excel adds a serial number after the name of the pivot table.
>
> Am I searching in the right direction or is there a much simpler
> solution?

Never mind.
John Walkenbach, Power Programming, page 797: Using VBA to write VBA
code

[Pawel Niewiadomski]

Amedee Van Gasse
<nzrqrr.qvgzntjrt.in...@orfgnngavrg.rztebhc.qbabgfcnz.or
> wrote in news:Xns952193145D4DFam...@194.109.133.29:

> Amedee Van Gasse schreef in
> <news:Xns95218ACEDB1Eam...@194.109.133.29>:
>

> Never mind.
> John Walkenbach, Power Programming, page 797: Using VBA to write VBA
> code
>

Could you (or anyone) please summarize more or less what is written in
the book. I have a similar problem and I have not been able to solve it
up till now. I would be very grateful.
Thanks in advance,
Pawel