Gurus: What's wrong with this code?

[Robert Stober]

Hi,

I've been over and over it, and I can't find anything wrong. But it doesn't
work. It crashes Excel every time. I got it from a smart guy named Chip
Pearson (thank you Chip). To be specific, I added the variables inside the
named "String" arguments, but it didn't work before I did that either. It
*almost* works, then crashes...

' Insert SelectionChange event procedure
With
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Worksheets("Contents").
CodeName).CodeModule
.InsertLines Line:=.CreateEventProc("SelectionChange","Worksheet") + 1,
_
String:="If Not Intersect(Target, Range(""" & nextCell & """)) Is
Nothing Then" & VbCrLF & _
" Charts(""" & chartName & """).Activate" & VbCrLF & _
"End If"
End With


Any ideas?

Thank you,

Robert Stober

 

[Tom Ogilvy]

Sub Tester1()
With ActiveWorkbook.VBProject.VBComponents( _
ActiveWorkbook.Worksheets("Contents"). _
CodeName).CodeModule
.InsertLines Line:=.CreateEventProc( _
"SelectionChange", "Worksheet") + 1, _
String:= _
"If Not Intersect(Target, Range(""B2""))" & _
" Is Nothing Then" & vbCrLf & _
" Charts(""Chart1"").Activate" & vbCrLf & _
"End If"
End With
End Sub


Worked fine for me.

 

[Robert Stober]

Tom,

Would it make any difference if this code was inside a macro that was called
from within a workbook_open event procedure? What I mean is that I have a
workbook_open procedure that calls a "CreateCharts" macro and this code is
in that macro. Do you think it would work in that situation? That's my
situation, and it's not working. What's more, even when I put in a stop
before this block, the block still runs and Excel still crashes...

Thank you,

Robert

 

[Tom Ogilvy]

Responded to private email.