Using a macro to remove a macro
 

I have this macro (below) that I would like to remove using another macro.
It is located in 'ThisWorkbook'.

Private Sub Workbook_Open()

What code can I use in a Macro to remove 'Private Sub Workbook_Open()'?

Thanks,
Paul

Using a macro to remove a macro
 

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

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("Workbook_Open", 0)
cLines = .ProcCountLines("Workbook_Open", 0)
.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



--
HTH

Bob Phillips

"PCLIVE" wrote in message
...
I have this macro (below) that I would like to remove using another macro.
It is located in 'ThisWorkbook'.

Private Sub Workbook_Open()

What code can I use in a Macro to remove 'Private Sub Workbook_Open()'?

Thanks,
Paul

Using a macro to remove a macro
 

Thanks Bob. You're right-on!
I really appreciate it.

Regards,
Paul

"Bob Phillips" wrote in message
...
'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("Workbook_Open", 0)
cLines = .ProcCountLines("Workbook_Open", 0)
.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



--
HTH

Bob Phillips

"PCLIVE" wrote in message
...
I have this macro (below) that I would like to remove using another
macro.
It is located in 'ThisWorkbook'.

Private Sub Workbook_Open()

What code can I use in a Macro to remove 'Private Sub Workbook_Open()'?

Thanks,
Paul

Using a macro to remove a macro
 

See Chip Pearson's site for info on this subject.

http://www.cpearson.com/excel/vbe.htm


Gord Dibben Excel MVP

On Fri, 1 Jul 2005 14:24:42 -0400, "PCLIVE" wrote:

I have this macro (below) that I would like to remove using another macro.
It is located in 'ThisWorkbook'.

Private Sub Workbook_Open()

What code can I use in a Macro to remove 'Private Sub Workbook_Open()'?

Thanks,
Paul