Deleting or Removing Modules 5

[FJAY]

I have an Excel VBA with Modules in it. I want to remove all the modules after a user clicks a commandbutton. How can I do this?

 

[Gavona]

It must be something like this but I can't get it to work

Sub testModule()
Dim Myvb
For Each Myvb In ThisWorkbook.VBProject.VBComponents
If Myvb.Name = "Module2" Then
MsgBox (Myvb.Name)
Myvb.Remove
End If
Next Myvb
End Sub

From Help:
Syntax

object.Remove(component)

The Remove syntax has these parts:

Part Description

object Required. An object expression that evaluates to an object in the Applies To list.

component Required. ... For the VBComponents collection, an enumerated constant representing a class module, a form, or a standard module.

Thanks,

Gavin

 

[yazman08]

Try this man, works for me........

Sub remove_modules()

With ActiveWorkbook.VBProject
.vbcomponents.Remove .vbcomponents("insert mod name")
'.vbcomponents.Remove .vbcomponents("")
'.vbcomponents.Remove .vbcomponents("")
End With


End Sub

 

[FJAY]

Good Morning - I can get this to work. It generated a run-time error '1004' message:

Method 'VBProject' of object '_Workbook' failed

Thanks.

 

[fumei]

Are you sure you have a reference to the VBA Extensibility?

Gerry

 

[Storyteller]

Hello All,
Check out this previous post.

Remove a Module with Code
thread707-853828

Regards,
Michael

 

[Gavona]

Great. References were my main problem so a star for fumei.
Finding a solution myself was more difficult because "In Excel97, these objects, methods, and properties are not described in the normal VBA help files. You need to open the file called VEENOB3.hlp." So thanks and a star to Storyteller as the link he provided directs you to Chip Pearson's site which told me about this (and had all the answers anyway).
This is the solution that I would suggest (assumes all code is in standard modules). Remarkably without the first IF statement the procedure will delete itself as well

Sub testModule()
Dim Myvb As VBComponent
For Each Myvb In ThisWorkbook.VBProject.VBComponents
If Myvb.Name <> "Module1" Then    
If Myvb.Type = vbext_ct_StdModule Then
MsgBox ("Deleting Standard module  " & Myvb.Name)

ThisWorkbook.VBProject.VBComponents.Remove Myvb

End If
End If
Next Myvb
End Sub

Thanks,

Gavin