Delete Code from Worksheet
 

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

Delete Code from Worksheet
 

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").Code Name)
End With
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Sub


JohnV wrote:

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

Delete Code from Worksheet
 

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

-----Original Message-----
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
.