Delete VB code from a worksheet

[Henk]

Within a Worksheet_Change macro on sheet A, I copy sheet A to Sheet A (2).
Directly after that, I want to remove all VB code from Sheet A (2).

How do I do that?

Thanks in advance,

Henk

 

[Henk]

Joel,

Thanks for your prompt reply. I had been there before and after my visit I
manged to delete Modules and Forms from my VBA projects, but now I want to
delete all code from a specific Worksheet. Chip does not tell how to do that.
Any idea?

Regards,

Henk

 

[Joel]

I modified slight some of chips code. This clears sheet1 code.


Sub DeleteProcedureFromModule()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim StartLine As Long
Dim NumLines As Long
Dim ProcName As String

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule


With CodeMod
.DeleteLines StartLine:=1, Count:=.CountOfLines
End With
End Sub

 

[Henk]

Joel,

I embedded tho code you sent me in my code. When I start the code I get the
message :

"Excel has stopped working"
"Windows is checking for a solution to this problem" :

After a little while :

"Microsoft Office Excel is trying to recover your information"
"This might take a few minutes"

After another little while :

"Microsoft Excel is restarting"

And then my file opens again. With the new sheet and ...... without the VB
code.

So, somehow it works but not the way it should, I think.

Another thing is that I now know that the new sheet is called Sheet2,
because I can see that in VB. But how do I know the name when a second copy
or third is made, by someone else on another PC? Can I ask for the name of
the new sheet in VB?

I am struggling further. Hope you can struggle with me.

Kind regards,

Henk

 

[Joel]

if you are trying to delete specific subroutine by name of subroutine you can
go through the enire workbook project and search for thsoe macro by name.
Do you want users to insert there own macros into the workbook? Do you want
the user macros also to be deleted?

I don't put my macros on thisworkbook and sheets unless it is an event
macro. All my macros go into modules. I'm not sure what macros you are
trying to delte from the sheets. You may want to delete all macro from the
sheets and leave the macros in the module alone.

 

[Henk]

Joel,

It is a Change_Worksheet macro ons Sheet A that copies itself to sheet A
(2), which therefor has the same Change_Worksheet event macro, which is
started when I change something on Sheet A (2). So I think I will choose for
an "elegant" workaround by enabling events only when thes Sheet.Name = Sheet
A. So the code remains on Sheet A (2), but it will never be invoked.

Many thanks for your help. It's very much appreciated.

Regards,

Henk