Creating ThisWorkbook Event Procedures

[Art H]

I've created a macro that creates individual workbooks from all
worksheets within a multisheet workbook adding some event procedures to
the individual workbooks. I made use of Chip Pearson's page
"Programming To The Visual Basic Editor" to accomplish part of this
activity. The VBE IDE window flashes showing the ThisWorkbook module
that being created for each workbook being created even though I'm
using MainWindow.Visible = False as suggested by post "Creating an
event procedure question". How do I prevent VBE displaying the creation
of the procedures?

I have Excel 2000 (9.0.6926 SP-3).

Here's an outline of my code:
For each worksheet
Application.ScreenUpdating = False
worksheet.copy
Application.VBE.MainWindow.Visible = False
Add event workbook procedures SheetChange, BeforeSave, and
BeforeClose
disable events
SaveAs
Close
enable events
Application.ScreenUpdating = True
Next

Here's the details of adding the events, which _do_ work in the created
workbooks:
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("SheetChange", "Workbook") + 1
.InsertLines StartLine, _
" Msgbox ""Please make changes to the Master file."" & vbCr &
""Your changes will not be saved."",vbExclamation+vbOkOnly" & vbCr & _
" Saved = True"

StartLine = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines StartLine, _
" Msgbox ""Will not save changes."",vbExclamation+vbOkOnly" &
vbCr & _
" Cancel = True" & vbCr & _
" Saved = True"

StartLine = .CreateEventProc("BeforeClose", "Workbook") + 1
.InsertLines StartLine, _
" Saved = True"
End With

 

[Jim Cone]

Try moving the ScreenUpdating lines outside the loop.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


I've created a macro that creates individual workbooks from all
worksheets within a multisheet workbook adding some event procedures to
the individual workbooks. I made use of Chip Pearson's page
"Programming To The Visual Basic Editor" to accomplish part of this
activity. The VBE IDE window flashes showing the ThisWorkbook module
that being created for each workbook being created even though I'm
using MainWindow.Visible = False as suggested by post "Creating an
event procedure question". How do I prevent VBE displaying the creation
of the procedures?

I have Excel 2000 (9.0.6926 SP-3).

Here's an outline of my code:
For each worksheet
Application.ScreenUpdating = False
worksheet.copy
Application.VBE.MainWindow.Visible = False
Add event workbook procedures SheetChange, BeforeSave, and
BeforeClose
disable events
SaveAs
Close
enable events
Application.ScreenUpdating = True
Next

Here's the details of adding the events, which _do_ work in the created
workbooks:
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("SheetChange", "Workbook") + 1
.InsertLines StartLine, _
" Msgbox ""Please make changes to the Master file."" & vbCr &
""Your changes will not be saved."",vbExclamation+vbOkOnly" & vbCr & _
" Saved = True"

StartLine = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines StartLine, _
" Msgbox ""Will not save changes."",vbExclamation+vbOkOnly" &
vbCr & _
" Cancel = True" & vbCr & _
" Saved = True"

StartLine = .CreateEventProc("BeforeClose", "Workbook") + 1
.InsertLines StartLine, _
" Saved = True"
End With

 

[Art H]

Jim,

Thanks for the idea. Nope, it did not work.

Art