Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Dynamically Writing Event Procedure
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).CodeModu le 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Dynamically Writing Event Procedure
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 procedu 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").CodeM odule 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 wrote: 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).CodeModu le 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing a formula which dynamically generates results | Excel Worksheet Functions | |||
change event procedure | Excel Programming | |||
programming the VBE for a new event procedure | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming | |||
ComboBox Event Procedure problem | Excel Programming |