Delete Code from Worksheet

[JohnV]

I have a workbook with a button that copies itself to a
new workbook. I then delete the button from the new
workbook but the code remains. In the VBA Editor I can
see the code attached to the worksheet.

Is there a way to delete the code or perhaps copy the
worksheet from the original workbook without the button
and associated code?

Thanks,
JohnV

 

[Dave Peterson]

Stolen from Chip Pearson's site:
http://www.cpearson.com/excel/vbe.htm

Option Explicit
Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent

With ActiveWorkbook
Set VBComp = .VBProject.VBComponents(.Worksheets("sheet1").CodeName)
End With
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Sub

 

[Greg Wilson]

Try the following code experimentally. The code has not
been tested in the scenario you describe.

Assumptions:
1) The workbook that needs code deletion is the active
workbook.
2) The code module containing the code pertains to the
active sheet.
3) There are 5 code lines for the button.

Dim CM As Object, StartLine As Integer
With Application.VBE.ActiveVBProject
Set CM = .VBComponents(ActiveSheet.CodeName).CodeModule
StartLine = CM.CountOfLines + 1
.VBComponents(ActiveSheet.CodeName).CodeModule _
.DeleteLines StartLine, 5
End With

Regards,
Greg