Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | |
#3
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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove code from behind worksheet | Excel Programming | |||
Remove / Add code through VBA | Excel Programming | |||
VBA Code to remove VBA | Excel Programming | |||
Use VB code to remove code in sheet1 | Excel Programming | |||
Remove VBA code | Excel Programming |