I'm using the following sub to programmatically add an event procedure to
the worksheet of a newly created workbook:

-------------------------

Private Sub WSEventCode(wbOut as workbook, strWsName as string)
'add event code to worksheet that is called when pivot table
recalculates
'function of event code is to limit width of pivot table columns to 14
and cell wrap the column headers
Open "MyWsEvent" For Output As #1
Print #1, "Private Sub Worksheet_PivotTableUpdate(ByVal Target As
PivotTable)"
Print #1, "Dim ws As Worksheet, yMax As Long, xMax As Integer, i As
Integer, objStartCell As Range"
Print #1, "With Target.TableRange1"
Print #1, " yMax = .Rows.Count"
Print #1, " xMax = .Columns.Count"
Print #1, " Set objStartCell = .Cells(1, 1)"
Print #1, "End With"
Print #1, "'set all but the left column to wrap text with a max col
width of 14'"
Print #1, "For i = 1 To (xMax - 1)"
Print #1, " With Columns(objStartCell.Offset(0, i).Column)"
Print #1, " If .ColumnWidth 14 Then"
Print #1, " .ColumnWidth = 14"
Print #1, " .WrapText = True"
Print #1, " End If"
Print #1, " End With"
Print #1, "Next"
Print #1, "End Sub"
Close #1
wbOut.VBProject.VBComponents(Worksheets(strWsName) .CodeName).CodeModule.AddFromFile
"MyWsEvent"
End Sub

-------------------------

The problem is that Excel crashes when I run it on the last line. I'm
running Excel XP/WinXP SP2. The crash error reporting suggests a problem
with vb6.dll, which is the Tools/Reference to Microsoft Visual Basic for
Applications Extensibility 5.3.

Is this a known bug and is there a workaround?

Regards,
Wayne Cressman

I didn't try your code, but I think I might just try to write directly to the
module:

Chip Pearson has a nice sample at:
http://www.cpearson.com/excel/vbe.htm



The Vision Thing wrote:

I'm using the following sub to programmatically add an event procedure to
the worksheet of a newly created workbook:

-------------------------

Private Sub WSEventCode(wbOut as workbook, strWsName as string)
'add event code to worksheet that is called when pivot table
recalculates
'function of event code is to limit width of pivot table columns to 14
and cell wrap the column headers
Open "MyWsEvent" For Output As #1
Print #1, "Private Sub Worksheet_PivotTableUpdate(ByVal Target As
PivotTable)"
Print #1, "Dim ws As Worksheet, yMax As Long, xMax As Integer, i As
Integer, objStartCell As Range"
Print #1, "With Target.TableRange1"
Print #1, " yMax = .Rows.Count"
Print #1, " xMax = .Columns.Count"
Print #1, " Set objStartCell = .Cells(1, 1)"
Print #1, "End With"
Print #1, "'set all but the left column to wrap text with a max col
width of 14'"
Print #1, "For i = 1 To (xMax - 1)"
Print #1, " With Columns(objStartCell.Offset(0, i).Column)"
Print #1, " If .ColumnWidth 14 Then"
Print #1, " .ColumnWidth = 14"
Print #1, " .WrapText = True"
Print #1, " End If"
Print #1, " End With"
Print #1, "Next"
Print #1, "End Sub"
Close #1
wbOut.VBProject.VBComponents(Worksheets(strWsName) .CodeName).CodeModule.AddFromFile
"MyWsEvent"
End Sub

-------------------------

The problem is that Excel crashes when I run it on the last line. I'm
running Excel XP/WinXP SP2. The crash error reporting suggests a problem
with vb6.dll, which is the Tools/Reference to Microsoft Visual Basic for
Applications Extensibility 5.3.

Is this a known bug and is there a workaround?

Regards,
Wayne Cressman

--

Dave Peterson