How to clear the VBA code of a worksheet via a macro?

Question

I have a file where there's a template sheet that needs to run some code when it's activated. This sheet is being duplicated to create sheets that don't need to run this code. Currently, I have the code to check for worksheet's codename when run so that it does nothing on extra sheets, but it still slows usage down when you switch between sheets.

Is there any way to make the macro that makes duplicates also clear their VBA code contents?

(Edit) Please note that the code I need to clear is not in a module. After some research, it seems I found a way to remove modules (by accessing VBProject.VBComponents), but I'm not sure how to access the VBA code of a worksheet.

Answer 1

To remove complete code in all Sheet modules you could try something like this:

Sub Remove_some_vba_code()

Dim activeIDE As Object 'VBProject
Set activeIDE = ActiveWorkbook.VBProject

Dim Element As VBComponent

Dim LineCount As Integer
For Each Element In activeIDE.VBComponents
    If Left(Element.Name, 5) = "Sheet" Then    'change name if necessary
        LineCount = Element.CodeModule.CountOfLines

        Element.CodeModule.DeleteLines 1, LineCount
    End If
Next

End Sub

Answer 2

Another way you could approach this is to keep all of your code out of the worksheet. Then you don't have to delete anything. The worksheet's code module is a handy place to code events, but you can create your own class module to handle events too. Put this in a standard module:

Public gclsEvent As CEvent

Sub Auto_Open()

    Set gclsEvent = New CEvent
    Set gclsEvent.This = Sheet1

End Sub

This will create an instance of CEvent that's global, so it won't lose scope as long as your workbook is open. It assigns the worksheet codenamed Sheet1 to the This property of the class. Create a class module named CEvent with this code

Private WithEvents mwsThis As Worksheet

Public Property Set This(ByVal wsThis As Worksheet): Set mwsThis = wsThis: End Property
Public Property Get This() As Worksheet: Set This = mwsThis: End Property

Private Sub mwsThis_Activate()

    Me.This.Copy , Me.This.Parent.Sheets(Me.This.Parent.Sheets.Count)

End Sub

The WithEvents keyword exposes events for that object. Since we're only hooking up the events for Sheet1, activating another sheet won't trigger the code.