Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete object code | Excel Discussion (Misc queries) | |||
Delete code | Excel Discussion (Misc queries) | |||
delete code keeps going and coing | Excel Discussion (Misc queries) | |||
How to code to insert / delete any worksheet? | Excel Worksheet Functions | |||
VBA code to delete rows | Excel Discussion (Misc queries) |