Using VB, remove a certain section from ThisWorkbook
 

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
--

Using VB, remove a certain section from ThisWorkbook
 

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)




On Wed, 19 Nov 2008 11:14:31 -0500, "PCLIVE"
wrote:


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

Using VB, remove a certain section from ThisWorkbook
 

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



--

"Chip Pearson" wrote in message
...
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)




On Wed, 19 Nov 2008 11:14:31 -0500, "PCLIVE"
wrote:


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

Using VB, remove a certain section from ThisWorkbook
 

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

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



PCLIVE wrote:

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

--

"Chip Pearson" wrote in message
...
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)




On Wed, 19 Nov 2008 11:14:31 -0500, "PCLIVE"
wrote:


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

--

Dave Peterson

Using VB, remove a certain section from ThisWorkbook
 

Thanks for pointing me in the right direction Dave.

Regards,
Paul

--

"Dave Peterson" wrote in message
...
Read Chip's page:
http://www.cpearson.com/excel/vbe.aspx

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



PCLIVE wrote:

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

--

"Chip Pearson" wrote in message
...
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)




On Wed, 19 Nov 2008 11:14:31 -0500, "PCLIVE"
wrote:


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

--

Dave Peterson

Using VB, remove a certain section from ThisWorkbook
 

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)





On Wed, 19 Nov 2008 12:48:42 -0500, "PCLIVE"
wrote:

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

Using VB, remove a certain section from ThisWorkbook
 

Thanks Chip. I agree with good programming practice.

Your help is appreciated, as always.
Paul

--

"Chip Pearson" wrote in message
...
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)





On Wed, 19 Nov 2008 12:48:42 -0500, "PCLIVE"
wrote:

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