Hi,

Pls help me with the following problem:

I have a lot of workbooks (75) in which I need to insert a BeforeClose Event.
Can you help me to do it programatically?

Thanks a lot

'----------------------------------------------------------------
Sub AddWorkbookEventProc()
'----------------------------------------------------------------
Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("BeforeClose", "Workbook") + 1
.InsertLines StartLine, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

End Sub




--
__________________________________
HTH

Bob

"Catalin" wrote in message
...
Hi,

Pls help me with the following problem:

I have a lot of workbooks (75) in which I need to insert a BeforeClose
Event.
Can you help me to do it programatically?

Thanks a lot

Hi Catalin,

You will need to start by giving yourself access to the VBA project object
model. Go to Excel Options, Trust Center, in the Macro Settings. This is so
that you can write code that writes code. Probably worth removing access once
you're done.

You will also have to reference the Microsoft Visual Basic for Applications
Extensibility (version number) library (Tools References in the VBA
editor) in the book that contains the code you are writing.

You can then create a module that contains the code you wish to copy. Using
the VBA project object model you will be able to access the code written
within your modules by referring to them (CodeModules):

Set MyCodeToCopyModule = _
ThisWorkbook.VBProject.VBComponents_
("ModuleNameToCopyFrom").CodeModule

You can now look inside that module for the procedure you've written to copy
the lines from it.

To refer to part of the workbook you can use a VBComponent, so use something
like:

Set wbkToAddCodeTo = (loop through your 75 workbooks here)

Set MyBookComponent = _
wbkToAddCodeTo.VBProject.VBComponents("ThisWorkboo k")

You can then use methods like:
vbc.CodeModule.AddFromString
vbc.CodeModule.InsertLines
vbc.CodeModule.AddFromFile

to start adding code to the ThisWorkbook module.

These methods will be fine if your ThisWorkbook modules are empty. If they
do contain code then you should check that the BeforeClose event is not
already the

On Error Resume Next
lngFirstLine = vbc.CodeModule.ProcStartLine _
("Workbook_BeforeClose", vbext_pk_Proc)
If Err.Number < 0 Then
On Error GoTo 0
'ERROR MEANS EVENT HANDLER CODE PROC NOT PRESENT
Else
On Error GoTo 0
'IS PRESENT SO DELETE IT OR ADD TO IT
End If

Once you have the line number from above you can use methods to start
writing at these line numbers. The InsertLines method takes a line number as
a parameter, so you could copy a line at a time and insert it at the
appropriate place, looping from the first line to the last (use
ProcCountLInes to loop).

Obviously, at some point you will need to use some of the above lines to
locate the code you are going to copy, so if you call it

Private Sub CopyCodeFromHere()

you can locate the lines using:

MyCodeToCopyModule.ProcStartLine _
("CopyCodeFromHere", vbext_pk_Proc)

Looping through all lines in this procedure and copying them across one at a
time.

I hope this helps a bit! Good luck.

Cheers,

Sean.




--
(please remember to click yes if replies you receive are helpful to you)


"Catalin" wrote:

Hi,

Pls help me with the following problem:

I have a lot of workbooks (75) in which I need to insert a BeforeClose Event.
Can you help me to do it programatically?

Thanks a lot