I get an Excel runtime error executing

loc = vbc.CodeModule.CreateEventProc("Change", "Worksheet")

as below.

Application.EnableEvents = False
For Each vbc In ActiveWorkbook.VBProject.VBComponents
If vbc.name = Worksheets(1).codeName Then
loc = vbc.CodeModule.CreateEventProc("Change", "Worksheet")
vbc.CodeModule.InsertLines loc, wsChangeEvent
Exit For
End If
Next
Application.EnableEvents = True

Using the same code to insert for example an Workbook Open event procedure
works just fine. Anybody know if this is a known problem- or perhaps I am
doing something I shouldn't do ?

I'm using Excel 2002 SP3.

Regards
ojv

the problem is the name of variable "loc" ...
Function Loc(FileNumber As Integer) As Long
of VBA.FileSystem

you use
dim loc as long

or changes its name to the variable


regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"ojv" wrote:

> I get an Excel runtime error executing
>
> loc = vbc.CodeModule.CreateEventProc("Change", "Worksheet")
>
> as below.
>
> Application.EnableEvents = False
> For Each vbc In ActiveWorkbook.VBProject.VBComponents
> If vbc.name = Worksheets(1).codeName Then
> loc = vbc.CodeModule.CreateEventProc("Change", "Worksheet")
> vbc.CodeModule.InsertLines loc, wsChangeEvent
> Exit For
> End If
> Next
> Application.EnableEvents = True
>
> Using the same code to insert for example an Workbook Open event procedure
> works just fine. Anybody know if this is a known problem- or perhaps I am
> doing something I shouldn't do ?
>
> I'm using Excel 2002 SP3.
>
> Regards
> ojv