How do I use the same BeforeClose event on multiple workbooks

[Jenny]

Originally I asked how to prevent users from closing a
workook using the close button. The answer was to put
some code in the workbook's BeforeClose event.

If a new workbook is opened during program execution, how
do I add this code to that workbook's BeforeClose event
without going into VB and adding it? Can that be done
through code?

~Jenny

 

[Bob Phillips]

Jenny,

Here is some code to create a BeforeClose event in the active workbook.

You will need to work out how to invoke it, and add the real code, not just
my Msgbox

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CreateEventProc("BeforeClose", "Workbook") + 1, _
vbTab & "If Range(""A1"").Value > 0 Then " & vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"

End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)