inserting code into code module crashes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kris - 24 Jan 2006 21:35 GMT

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?

Reply to this Message

Peter T - 25 Jan 2006 00:24 GMT

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

> There is a beautiful code
>
[quoted text clipped - 32 lines]
>
> What am I doing wrong?

Reply to this Message

Kris - 25 Jan 2006 15:20 GMT

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

> 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.
[quoted text clipped - 41 lines]
>>
>>What am I doing wrong?

Reply to this Message

Peter T - 25 Jan 2006 16:17 GMT

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

> It is added to a new workbook and doesn't work.
>
[quoted text clipped - 43 lines]
> >>
> >>What am I doing wrong?

Reply to this Message

Peter T - 25 Jan 2006 16:21 GMT

> 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

> Your code works fine for me. All I did is
>
[quoted text clipped - 47 lines]
> > >>ActiveSheet.PivotTables(""pivottable1"")"
> > >> .InsertLines .CountOfLines + 1, "

Worksheet_PivotTableUpdate

> > >>TargetPivotTable"
> > >> .InsertLines .CountOfLines + 1, "ErrHandler:"
[quoted text clipped - 7 lines]
> > >>
> > >>What am I doing wrong?

Reply to this Message

Kris - 25 Jan 2006 16:40 GMT

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

>>On which point - how/where did you define "Sheet1"
>
[quoted text clipped - 82 lines]
>>>>>
>>>>>What am I doing wrong?

Reply to this Message

Peter T - 25 Jan 2006 16:57 GMT

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

> I skipped not important code.
> All workbooks, workseets are created correctly, sModuleName is not
[quoted text clipped - 123 lines]
> >>>>>
> >>>>>What am I doing wrong?

Reply to this Message

Kris - 25 Jan 2006 17:17 GMT

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

> As I said, the code you posted works fine for me, inserting an event proc
> for ComboBox1 into a WorkSheet module.
[quoted text clipped - 133 lines]
>>>>>>>
>>>>>>>What am I doing wrong?

Reply to this Message

Kris - 25 Jan 2006 17:28 GMT

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.

> As I said, the code you posted works fine for me, inserting an event proc
> for ComboBox1 into a WorkSheet module.
[quoted text clipped - 133 lines]
>>>>>>>
>>>>>>>What am I doing wrong?

Reply to this Message