inserting code into code module crashes

[Kris]

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?

 

[Peter T]

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]

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

 

[Peter T]

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

 

[Peter T]

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

 

[Kris]

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]

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]

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

 

[Kris]

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.