adding to a macro with a macro

Rob

How to do this depends on a couple of things:
- Are the projects password protected in any of the files to which
you wish to add the code?
- Is there any existing code in any of the ThisWorkbook modules - if there
is in some and not in others, that too will make a difference.

Code will not take long, once these issues are addressed.

Regards

Here is some code I did a while ago that should
help change as required.......
Change it in the routine procedure
Note: This adds the worksheet change event

Sub Modify_Modules()

Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return

Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your Event Procedure OR SubRoutine
SubName = "Private Sub Workbook_SheetChange(ByVal Sh As Object," & _
"ByVal Target As Excel.Range)" & LF

'Your Procedure
Proc = "If Target.Row = 1 Then" & LF
Proc = Proc & _
Tabs & "MsgBox " & Ap & "Testing row number =" & Ap & " & Target.Address" & LF
Proc = Proc & _
"End If" & LF

'Use ActiveWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With

Set ModEvent = Nothing

End Sub

'Ivan

wow you guys are fast :)

1. No project passwords
2. There is some existing code. I would like to put my addition at the end of it all if possible. Most of the workbooks will be the same, BUT there may be some that have no code in the "thisworkbook" section

Thanks again! i should be able to get it from this!

Question Ivan

This doesnt really relate to my original question, but why did you redefine "Proc" multiple times? couldnt you have just put:
Proc = ...all your code

instead of doing part then redfining it? was there a reason for this?

Just wondering :)

Re: Question Ivan

Yes you can...BUT I did it like this to make
it easier to read and then change....your note
that to change a line for the procedure
all you have to do is change 1 line
Also there is a limit......

Just do it the way that you find easiest....I just
did it like this for readability and so that
I could change see what was happening line by line.

Ivan

oh I see, another question if you dont mind...

I modified the code and just tried it, but im getting the compile error on the definition of:
Dim ModEvent As CodeModule 'Module to Modify
its telling me :
user-defined type not defined

It is not recognizing CodeModule as a definition. I know I've had this problem before, and there is a way to add it, but i cant find it. Can you tell me? Thanks a million!

nevermind...Thanks!

i changed Codemodule to object and it worked! thanks!