PDA

View Full Version : Using Macro to Add Code



gowthron
07-08-2010, 02:45 AM
Folks,

I don't know if this is possible so I thought I would post the question and see if anyone has done anything similar before.

A writing a huge macro with lots of different functions and procedures which will be applied to a basic data.xls sheet. (Macro contained in another workbook which is referenced through toolbar button)

Users are looking for some validation to be performed when someone has updated the sheet and they then try to save and close.

My approach for this type of validation would be to add code into the Workbook_BeforeClose Event.

Is it possible to use a macro from one workbook to insert code into another worrkbook and particular event?

Thanks for any help in advance!

Cheers,

G.

Bob Phillips
07-08-2010, 04:21 AM
Here is an example



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

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").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

gowthron
07-08-2010, 05:18 AM
Xld,

Cheers for getting back on this. That code creates the code fine but keep getting the popup message:

"Can't enter break mode at this time"

Options are to continue (Creates code), End or Help. Any ideas to stop this appearing?

Cheers,

G.

gowthron
07-08-2010, 05:33 AM
XLD,

Not to worry. This message only appears when stepping through the code. If you run it as a Macro it runs fine.

Thanks for your help!

G. :bow: