Jon,

Here is an amended version.

One question, is it an event procedure you are deleting/adding as that is
different?

Const vbext_pk_Proc = 0

Sub Control()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
DeleteProcedure sh.CodeName, "MyProc"
AddProcedure sh.CodeName, "MyProc"
Next sh
End Sub

'----------------------------------------------------------------
Sub DeleteProcedure(module As String, proc As String)
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents(module).Code Module
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine(proc, vbext_pk_Proc)
cLines = .ProcCountLines(proc, vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub


'----------------------------------------------------------------
Sub AddProcedure(module As String, proc As String)
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents(module).Code Module
With oCodeModule
cLines = .CountOfLines + 1
.InsertLines cLines, _
"" & vbCrLf & _
"Sub " & proc & "()" & vbCrLf & _
" Msgbox ""Here is the new procedure"" " & vbCrLf & _
" Call JonDidNotMessUp" & vbCrLf & _
"End Sub"
End With

End Sub




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon" wrote in message
...
Exactly what I was looking for. Thanks a bunch! However, I have one more
question:

I need to be able to do this to every sheet module in the workbook and I
won't know the name of each sheet module (could be sheet1 or sheet24
etc...).
Is there a way to cycle through the Sheet modules without knowing how
many
there are and what their names may be, and then apply the previous
subroutines?

"Bob Phillips" wrote:

Mr Jon,

This first routine deletes a procedure called MyMacro from a module
called
Module1, and the second adds a procedure called MyProc to that module.
Change the procedure name and module name to your values, and also amend
the
code in the second routine to your code.


Const vbext_pk_Proc = 0

'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents("Module1").C odeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub


'----------------------------------------------------------------
Sub AddProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim cLines As Long

Set oCodeModule =
ActiveWorkbook.VBProject.VBComponents("Module1").C odeModule
With oCodeModule
cLines = .CountOfLines + 1
.InsertLines cLines, _
"" & vbCrLf & _
"Sub MyProc()" & vbCrLf & _
" Msgbox ""Here is the new procedure"" " & vbCrLf & _
" Call JonDidNotMessUp" & vbCrLf & _
"End Sub"
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon" wrote in message
...
Mr. Phillips,

Are the procedures that contain the code to replace all the same?
Yes they are all the same. What it boils down to is the fact that in
each
sheet module I mistyped the name of the Sub to call. The Subs
themselves
are
all fine (and not located in the sheet module).

Do you just want the code to replace the code, or also to iterate
through
the workbooks and then replace the code?
I know how to navigate through the workbooks. Assume a workbook is
open
and
all that needs to be done is to iterate through each sheet module and
find/replace the necessary line in each.

"Bob Phillips" wrote:

Jon,

A few questions.

Are the procedures that contain the code to replace all the same? It
would
be far easier to delete the procedures and install new versions, but
this is
only possible if they are all the same

Do you just want the code to replace the code, or also to iterate
through
the workbooks and then replace the code?

If the answer to the latter is to get the workbooks as well, are
they
all in
on folder, or in folders within a folder, or all over the place?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon" wrote in message
...
Perhaps my initial question was poorly phrased. Let me try
stating
this
once
again.

Situation:
I have over 300 workbooks which each have several sheet modules
with
one
line of bad code. I need to be able to find ALL mathcing
incorrect
lines
and
replace them with correct lines. I would like to do this
programmatically.

e.g.
every sheet module has the line:
Call foo1
I need to replace that line in ALL the sheet modules with
Call foo2

Any help would be greatly appreciated.

"Jon" wrote:

All,
I made a small mistake in one line of code of which there are
a
trillion
copies. Basically, I want to write code to find and replace
lines
in
the
sheet modules.

ex. The sheet module has a line of code
Call JonMessedUp

I want to find all instances of that line in all the sheet
modules
and
change them to
Call JonDidNotMessUp

That's it.
Thanks in advance!!

--
J