Problem with Dynamically Writing Event Procedure

[The Vision Thing]

I'm using the following routine to add event procedures to a newly created
workbook wbOut from an existing workbook.

Private Sub AddEventProc(wbOut As Workbook)
'writes an event procedure to every sheet of the active workbook
On Error Resume Next
Dim ws As Worksheet
Dim LineNum As Long
For Each ws In wbOut.Worksheets
With wbOut.VBProject.VBComponents(ws.CodeName).CodeModule
LineNum = .CreateEventProc("PivotTableUpdate", "Worksheet")
.InsertLines LineNum + 1, _
"Target.ColumnRange.ColumnWidth = 14" & vbCrLf & _
"Target.ColumnRange.WrapText = True"
End With
Next ws
End Sub

The problem is that the function seems to open up the Visual Basic Editor
and leave it open. I don't want my clients to see this when they run the
routine. Is there a way to stop the Visual Basic Editor opening up with
this routine or to programmatically shut it down once opened up.

Also, I've found that the function will very intermittently cause Excel 2003
to crash. I've studied the Pierson page on using code to write code, but I
haven't found a way yet that doesn't intermittently crash Excel. Anybody
have any suggestions?

Thanks,
Wayne C.

 

[Dave Peterson]

I don't have a guess at why it crashes excel. But you could hide that VBE
window when you're done:

Application.VBE.MainWindow.Visible = False

And if you're adding the same code to all the worksheet modules, maybe you could
just use one procedure:

Option Explicit

Private Sub AddEventProc(wbOut As Workbook)
'writes an event procedure to every sheet of the active workbook
'On Error Resume Next 'what causes the error???

Dim LineNum As Long

With wbOut.VBProject.VBComponents("thisworkbook").CodeModule
LineNum = .CountOfLines + 1
.CreateEventProc "SheetPivotTableUpdate", "Workbook"
.InsertLines LineNum + 1, _
"sh.Target.ColumnRange.ColumnWidth = 14" & vbCrLf & _
"sh.Target.ColumnRange.WrapText = True"
End With

Application.VBE.MainWindow.Visible = False

End Sub

 

[The Vision Thing]

Thanks Dave, seems to have solved the problem for now.

Regards,
Wayne C.