inserting code into code module crashes

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >
> > >>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
> news:[email protected]...
>
>>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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>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" <[email protected]> wrote in message
news:[email protected]...
> 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
> > news:[email protected]...
> >
> >>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" <[email protected]> wrote in message
> >>news:[email protected]...
> >>
> >>>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" <[email protected]> wrote in message
> >>>>news:[email protected]...
> >>>>
> >>>>
> >>>>>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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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
>>>news:[email protected]...
>>>
>>>
>>>>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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>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" <[email protected]> wrote in message
>>>>>>news:[email protected]...
>>>>>>
>>>>>>
>>>>>>
>>>>>>>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 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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
>>>news:[email protected]...
>>>
>>>
>>>>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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>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" <[email protected]> wrote in message
>>>>>>news:[email protected]...
>>>>>>
>>>>>>
>>>>>>
>>>>>>>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?
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>
>
>