|
Posted to microsoft.public.excel.programming
|
|
|
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
|
|
|