Remove VBA code behind a worksheet
 

I have a worksheet with an event handler:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo xitsub

If Target.Address = "$W$7" Then
After_AIF

Else

End If
xitsub:
End Sub

The After_AIF function is in "Module1" which I delete when the workbook is
saved:

'Delete VB Code
Dim x As Object

Set x = Application.VBE.ActiveVBProject.VBComponents
x.Remove VBComponent:=x.Item("Module1")

My question is how do I delete the "Private Sub Worksheet_Change(ByVal
Target As Range)" sub? I'm getting what is apparently a compile error before
the "new" workbook closes.

I've tried:

x.Remove VBComponent:=x.Item("worksheet").code and several variants of the
VBComponenet:= thingy without success..

TIA

ABC

Remove VBA code behind a worksheet
 

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


--
Jim
"BAC" wrote in message
...
|I have a worksheet with an event handler:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| On Error GoTo xitsub
|
| If Target.Address = "$W$7" Then
| After_AIF
|
| Else
|
| End If
| xitsub:
| End Sub
|
| The After_AIF function is in "Module1" which I delete when the workbook is
| saved:
|
| 'Delete VB Code
| Dim x As Object
|
| Set x = Application.VBE.ActiveVBProject.VBComponents
| x.Remove VBComponent:=x.Item("Module1")
|
| My question is how do I delete the "Private Sub Worksheet_Change(ByVal
| Target As Range)" sub? I'm getting what is apparently a compile error
before
| the "new" workbook closes.
|
| I've tried:
|
| x.Remove VBComponent:=x.Item("worksheet").code and several variants of the
| VBComponenet:= thingy without success..
|
| TIA
|
| ABC
|
|

Remove VBA code behind a worksheet
 

Thanx, preciate it!

"Jim Rech" wrote:

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


--
Jim
"BAC" wrote in message
...
|I have a worksheet with an event handler:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| On Error GoTo xitsub
|
| If Target.Address = "$W$7" Then
| After_AIF
|
| Else
|
| End If
| xitsub:
| End Sub
|
| The After_AIF function is in "Module1" which I delete when the workbook is
| saved:
|
| 'Delete VB Code
| Dim x As Object
|
| Set x = Application.VBE.ActiveVBProject.VBComponents
| x.Remove VBComponent:=x.Item("Module1")
|
| My question is how do I delete the "Private Sub Worksheet_Change(ByVal
| Target As Range)" sub? I'm getting what is apparently a compile error
before
| the "new" workbook closes.
|
| I've tried:
|
| x.Remove VBComponent:=x.Item("worksheet").code and several variants of the
| VBComponenet:= thingy without success..
|
| TIA
|
| ABC
|
|