Using VB, remove a certain section from ThisWorkbook

[PCLIVE]

Using the following code I can remove the VB code contained in ThisWorkbook.

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With


Is there a way that I can specify to remove only the Workbook_Open portion
of the ThisWorkbook code?
Example:

"Private Sub Workbook_Open()"
<some code>
End Sub


The rest of the code in ThisWorkbook I'd like to leave untouched. Can this
be done

Thanks,
Paul
--

 

[Chip Pearson]

Try

Sub DeleteWBOpen()
Dim CodeMod As VBIDE.CodeModule
Dim StartLine As Long
Dim ProcLen As Long

Set CodeMod =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With CodeMod
StartLine = .ProcStartLine("Workbook_Open", vbext_pk_Proc)
ProcLen = .ProcCountLines("Workbook_Open", vbext_pk_Proc)
.DeleteLines StartLine, ProcLen
End With
End Sub


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[PCLIVE]

Thanks Chip. Your code works and does what I need. However, I get an error
on the first Dim statement:

Dim CodeMod As VBIDE.CodeModule

Is this correct? I'm using XL2002.

If I remark that line, it still does the trick, though.

Thanks,
Paul



--

 

[Dave Peterson]

Read Chip's page:
http://www.cpearson.com/excel/vbe.aspx

Especially the introduction section where he writes about setting a reference.

 

[PCLIVE]

Thanks for pointing me in the right direction Dave.

Regards,
Paul

--

 

[Chip Pearson]

In VBA, go to the Tools menu, choose References, and scroll down to
"Microsoft Visual Basic For Applications Extensibility Library 5.3".
Put a check next to that entry and click OK. The file that is
referenced by that entry is where the objects lke CodeModule and the
rest of the extensibility library are defined.

Strictly speaking, you could write the line of code as

Dim CodeMod As CodeModule
' instead of
Dim CodeMod As VBIDE.CodeModule

The "VBIDE" qualifier isn't required. However, I think it is good
programming practice to reference the library of all non-standard
(i.e, non-Excel, non-Ofifice) libraries and I always use the library
name. It is a matter of personal style.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[PCLIVE]

Thanks Chip. I agree with good programming practice.

Your help is appreciated, as always.
Paul

--