It worked okay for me Les (apart from not running the OnTime macro).

I assume you must have Trust Access to VBProjects and so on.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Les Stout" wrote in message
...
Hi all, i am using Chip Pearsons code below to insert some code in a
worksheet. When i start the macro with F8 and then run through with F5
it works fine, however when i run the full code excel gives me a dialog
box and says "Sorry excel encountered a problem and has to close" and
then re-starts ??

Any help would be appreciated.

Sub BBBStdR()
'
On Error Resume Next
CreateEventProcedure
On Error Resume Next
Application.OnTime Now + TimeValue("00:00:01"), "MoveKTLToArchiveR"

End Sub

Sub CreateEventProcedure()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long, sh As String
Const DQUOTE = """" ' one " character
sh = ActiveWorkbook.ActiveSheet.CodeName
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(sh)
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("BeforeDoubleClick", "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, "Cancel = True"
.InsertLines LineNum, "Worksheets(""0908 RMT"").Activate"
.InsertLines LineNum, "Application.ActiveSheet.ShowAllData"
.InsertLines LineNum, "Cancel = True"
End With
End Sub


Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***