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?

As I said, the code you posted works fine for me, inserting an event proc
for ComboBox1 into a WorkSheet module.

The problem might be in code that you have not posted, eg inserting the
ActiveX combobox, which certainly could cause problems if inserting into
same project that's running the code.

Regards,
Peter T

"Kris" wrote in message
...
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?

It is inserted into new workbook, so it's not the case.
I don't know why it doesn't work.
Thanks anyway.




Peter T wrote:
As I said, the code you posted works fine for me, inserting an event proc
for ComboBox1 into a WorkSheet module.

The problem might be in code that you have not posted, eg inserting the
ActiveX combobox, which certainly could cause problems if inserting into
same project that's running the code.

Regards,
Peter T

"Kris" wrote in message
...

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).CodeMod ule


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 changed the order.
I add code before I insert combobox.
Now it works.


in previous code I inserted combobox and code was the last element
added to worksheet.








Peter T wrote:
As I said, the code you posted works fine for me, inserting an event proc
for ComboBox1 into a WorkSheet module.

The problem might be in code that you have not posted, eg inserting the
ActiveX combobox, which certainly could cause problems if inserting into
same project that's running the code.

Regards,
Peter T

"Kris" wrote in message
...

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).CodeMod ule


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?