There is a beautiful code

Private Sub ComboBox1_Change()
Dim TargetPivotTable As PivotTable
On Error GoTo ErrHandler
Set TargetPivotTable = ActiveSheet.PivotTables("pivottable1")
Worksheet_PivotTableUpdate TargetPivotTable
ErrHandler:
End Sub


This code pasted into worksheet module works fine

The same code inserted from VBA crashes during insertion at first
..insertLines.


Dim VBCodeMod As CodeModule
Set VBCodeMod = wkb.VBProject.VBComponents(sModuleName).CodeModule
With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " On Error GoTo ErrHandler"
.InsertLines .CountOfLines + 1, " Set TargetPivotTable =
ActiveSheet.PivotTables(""pivottable1"")"
.InsertLines .CountOfLines + 1, " Worksheet_PivotTableUpdate
TargetPivotTable"
.InsertLines .CountOfLines + 1, "ErrHandler:"
.InsertLines .CountOfLines + 1, "End Sub"



I noticed that:
inserting non-events procedures works.
Using CreateEventProc also crashes
inserting to ThisWorkbook module work, but inserting into specific
sheet doesn't

What am I doing wrong?

I haven't looked at your code but if you are adding code to the same project
it might trigger a re-compile and hence crash.

If all works fine adding similar code to "another" workbook that would be
the route to go.

Regards,
Peter T


"Kris" wrote in message
...
There is a beautiful code

Private Sub ComboBox1_Change()
Dim TargetPivotTable As PivotTable
On Error GoTo ErrHandler
Set TargetPivotTable = ActiveSheet.PivotTables("pivottable1")
Worksheet_PivotTableUpdate TargetPivotTable
ErrHandler:
End Sub


This code pasted into worksheet module works fine

The same code inserted from VBA crashes during insertion at first
.insertLines.


Dim VBCodeMod As CodeModule
Set VBCodeMod = wkb.VBProject.VBComponents(sModuleName).CodeModule
With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " On Error GoTo ErrHandler"
.InsertLines .CountOfLines + 1, " Set TargetPivotTable =
ActiveSheet.PivotTables(""pivottable1"")"
.InsertLines .CountOfLines + 1, " Worksheet_PivotTableUpdate
TargetPivotTable"
.InsertLines .CountOfLines + 1, "ErrHandler:"
.InsertLines .CountOfLines + 1, "End Sub"



I noticed that:
inserting non-events procedures works.
Using CreateEventProc also crashes
inserting to ThisWorkbook module work, but inserting into specific
sheet doesn't

What am I doing wrong?

It is added to a new workbook and doesn't work.




Peter T wrote:
I haven't looked at your code but if you are adding code to the same project
it might trigger a re-compile and hence crash.

If all works fine adding similar code to "another" workbook that would be
the route to go.

Regards,
Peter T


"Kris" wrote in message
...

There is a beautiful code

Private Sub ComboBox1_Change()
Dim TargetPivotTable As PivotTable
On Error GoTo ErrHandler
Set TargetPivotTable = ActiveSheet.PivotTables("pivottable1")
Worksheet_PivotTableUpdate TargetPivotTable
ErrHandler:
End Sub


This code pasted into worksheet module works fine

The same code inserted from VBA crashes during insertion at first
.insertLines.


Dim VBCodeMod As CodeModule
Set VBCodeMod = wkb.VBProject.VBComponents(sModuleName).CodeModule
With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " On Error GoTo ErrHandler"
.InsertLines .CountOfLines + 1, " Set TargetPivotTable =
ActiveSheet.PivotTables(""pivottable1"")"
.InsertLines .CountOfLines + 1, " Worksheet_PivotTableUpdate
TargetPivotTable"
.InsertLines .CountOfLines + 1, "ErrHandler:"
.InsertLines .CountOfLines + 1, "End Sub"



I noticed that:
inserting non-events procedures works.
Using CreateEventProc also crashes
inserting to ThisWorkbook module work, but inserting into specific
sheet doesn't

What am I doing wrong?

Your code works fine for me. All I did is

Set wkb = workbooks.add

and change sModuleName to "Sheet1" then your code as posted (after some
un-wrapping).

On which point - how/where did you define "Sheet1"

Regards,
Peter T


"Kris" wrote in message
...
It is added to a new workbook and doesn't work.




Peter T wrote:
I haven't looked at your code but if you are adding code to the same
project
it might trigger a re-compile and hence crash.

If all works fine adding similar code to "another" workbook that would
be
the route to go.

Regards,
Peter T


"Kris" wrote in message
...

There is a beautiful code

Private Sub ComboBox1_Change()
Dim TargetPivotTable As PivotTable
On Error GoTo ErrHandler
Set TargetPivotTable = ActiveSheet.PivotTables("pivottable1")
Worksheet_PivotTableUpdate TargetPivotTable
ErrHandler:
End Sub


This code pasted into worksheet module works fine

The same code inserted from VBA crashes during insertion at first
.insertLines.


Dim VBCodeMod As CodeModule
Set VBCodeMod = wkb.VBProject.VBComponents(sModuleName).CodeModule
With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub
ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " On Error GoTo ErrHandler"
.InsertLines .CountOfLines + 1, " Set TargetPivotTable =
ActiveSheet.PivotTables(""pivottable1"")"
.InsertLines .CountOfLines + 1, " Worksheet_PivotTableUpdate
TargetPivotTable"
.InsertLines .CountOfLines + 1, "ErrHandler:"
.InsertLines .CountOfLines + 1, "End Sub"



I noticed that:
inserting non-events procedures works.
Using CreateEventProc also crashes
inserting to ThisWorkbook module work, but inserting into specific
sheet doesn't

What am I doing wrong?

On which point - how/where did you define "Sheet1"

I meant

On which point - how/where did you assign a string to sModuleName

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Your code works fine for me. All I did is

Set wkb = workbooks.add

and change sModuleName to "Sheet1" then your code as posted (after some
un-wrapping).

On which point - how/where did you define "Sheet1"

Regards,
Peter T


"Kris" wrote in message
...
It is added to a new workbook and doesn't work.




Peter T wrote:
I haven't looked at your code but if you are adding code to the same
project
it might trigger a re-compile and hence crash.

If all works fine adding similar code to "another" workbook that would
be
the route to go.

Regards,
Peter T


"Kris" wrote in message
...

There is a beautiful code

Private Sub ComboBox1_Change()
Dim TargetPivotTable As PivotTable
On Error GoTo ErrHandler
Set TargetPivotTable = ActiveSheet.PivotTables("pivottable1")
Worksheet_PivotTableUpdate TargetPivotTable
ErrHandler:
End Sub


This code pasted into worksheet module works fine

The same code inserted from VBA crashes during insertion at first
.insertLines.


Dim VBCodeMod As CodeModule
Set VBCodeMod =
wkb.VBProject.VBComponents(sModuleName).CodeModule
With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub
ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " On Error GoTo
ErrHandler"
.InsertLines .CountOfLines + 1, " Set TargetPivotTable =
ActiveSheet.PivotTables(""pivottable1"")"
.InsertLines .CountOfLines + 1, "
Worksheet_PivotTableUpdate
TargetPivotTable"
.InsertLines .CountOfLines + 1, "ErrHandler:"
.InsertLines .CountOfLines + 1, "End Sub"



I noticed that:
inserting non-events procedures works.
Using CreateEventProc also crashes
inserting to ThisWorkbook module work, but inserting into specific
sheet doesn't

What am I doing wrong?

I skipped not important code.
All workbooks, workseets are created correctly, sModuleName is not
empty, null, etc.

if I replace RegionSheet.CodeName by "ThisWorkbook" it works.
if I insert just "Sub a end sub" it works.
if I insert event procedure it fails.




-------------------
Dim wkBook As Workbook
Set wkBook = AddWorkbook(...)

Dim RegionSheet As Worksheet
Set RegionSheet = AddWorksheet(wkBook, ...)


AddCode wkBook, RegionSheet.CodeName

-----------------

Sub AddCode(wkb As Workbook, sModuleName As String)

Dim VBCodeMod As CodeModule
Set VBCodeMod = wkb.VBProject.VBComponents(sModuleName).CodeModule


With VBCodeMod

.InsertLines .CountOfLines + 1, "Private Sub ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " On Error GoTo ErrHandler"
.InsertLines .CountOfLines + 1, " Set TargetPivotTable =
ActiveSheet.PivotTables(""pivottable1"")"
.InsertLines .CountOfLines + 1, " Worksheet_PivotTableUpdate
TargetPivotTable"
.InsertLines .CountOfLines + 1, "ErrHandler:"
.InsertLines .CountOfLines + 1, "End Sub"

end with

end sub





Peter T wrote:
On which point - how/where did you define "Sheet1"


I meant

On which point - how/where did you assign a string to sModuleName

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...

Your code works fine for me. All I did is

Set wkb = workbooks.add

and change sModuleName to "Sheet1" then your code as posted (after some
un-wrapping).

On which point - how/where did you define "Sheet1"

Regards,
Peter T


"Kris" wrote in message
...

It is added to a new workbook and doesn't work.




Peter T wrote:

I haven't looked at your code but if you are adding code to the same

project

it might trigger a re-compile and hence crash.

If all works fine adding similar code to "another" workbook that would

be

the route to go.

Regards,
Peter T


"Kris" wrote in message
...


There is a beautiful code

Private Sub ComboBox1_Change()
Dim TargetPivotTable As PivotTable
On Error GoTo ErrHandler
Set TargetPivotTable = ActiveSheet.PivotTables("pivottable1")
Worksheet_PivotTableUpdate TargetPivotTable
ErrHandler:
End Sub


This code pasted into worksheet module works fine

The same code inserted from VBA crashes during insertion at first
.insertLines.


Dim VBCodeMod As CodeModule
Set VBCodeMod =

wkb.VBProject.VBComponents(sModuleName).CodeModule

With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub

ComboBox1_Change()"

.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " On Error GoTo

ErrHandler"

.InsertLines .CountOfLines + 1, " Set TargetPivotTable =
ActiveSheet.PivotTables(""pivottable1"")"
.InsertLines .CountOfLines + 1, "

Worksheet_PivotTableUpdate

TargetPivotTable"
.InsertLines .CountOfLines + 1, "ErrHandler:"
.InsertLines .CountOfLines + 1, "End Sub"



I noticed that:
inserting non-events procedures works.
Using CreateEventProc also crashes
inserting to ThisWorkbook module work, but inserting into specific
sheet doesn't

What am I doing wrong?