Hi,

I m trying to call a procedure say Proc1 in my code, which writes code
lines to a new worksheet for the events Activate and Change clubbed
together....
----------------------------------------------------
sub MYCODE
....
....
For each cell in CellRange
sheets.add after:=sheets(1)
call Proc1
next cell
....
....
End Sub
----------------------------------------------------
Sub Proc1
Dim StartLine As Double
With
ThisWorkbook.VBProject.VBComponents(ThisWorkbook.S heets(ActiveSheet.Name).CodeName).CodeModule
StartLine = .CreateEventProc("Activate", "Worksheet") + 1
.InsertLines StartLine + 0, "Dim r, c, AvailH, er, total, totemp
As Integer"
.InsertLines StartLine + 1, "Dim r1 As Range"
.InsertLines StartLine + 2, "Dim comstr As String"
.InsertLines StartLine + 3, "dt = Date"
.InsertLines StartLine + 4, "ActiveSheet.Unprotect"
.InsertLines StartLine + 5, "Range(Cells(1, ""B""), Cells(1,
Cells(1, ""B"").End(xlToRight).Column)).Select"
.InsertLines StartLine + 6, "Set f = Selection.Find(What:=dt,
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)"
.InsertLines StartLine + 7, "If Not f Is Nothing Then"
.InsertLines StartLine + 8, " c = f.Column"
.InsertLines StartLine + 9, "Else"
.InsertLines StartLine + 10, " c = 2"
.InsertLines StartLine + 11, "End If"
............
.............
..............
End Sub

****************** Now, heres the puzzling part *******************
Now, if the VBE editor window is open, the code gets pasted properly
to the activesheet, BUT, if the VBE editor is not open, then its
results in an error "Run Time error - 9. Subscript out of range".

When i click Debug, In the VBE code window its shows the 2nd line
above highlighted in yellow.
i.e.
With
ThisWorkbook.VBProject.VBComponents(ThisWorkbook.S heets(ActiveSheet.Name).CodeName).CodeModule

I m not sure, but i think this kind of error usually occurs due to
some sheets.count overflow or something...

Can anyone shed some light on this n how to rectify it......

I dont want to keep the VBE editor code window open due to security
reasons.

Regards,