Delete all modules and forms from workbook

[Guest]

Having difficulties posting - I fear I'm going to log on and see my question
8 times (please forgive me!).

Anyway, I want to delete all modules (about 20) and all forms (about 10) in
my workbook, and I am having great difficulty applying Chip Pearson's code.
I'm wondering if I don't have a necessary reference added - I continue to get
error messages.

Trying this line from Chip:
ThisWorkbook.VBProject.VBComponents("Module1").Name = "Module2"
returns this error:
"Programmatic access to Visual Basic Project is not trusted"

And this procedure:
Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("NewModule")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

returns this error:
"User-defined type not defined"

All my code is under the "Forms" folder and "Modules" folder, not under
sheets or "ThisWorkbook"

Can anyone see where I'm going wrong?

Thanks in advance
Daniel

 

[Norman Jones]

Hi Daniel,

"Programmatic access to Visual Basic Project is not trusted"

Tools | Macros | Security | Trusted publishers tab
check "Trust Access to Visual Basic Project"

"User-defined type not defined"

Chip Pearson's VBE prgramming page indicates:
'========================>
Before using these procedures, you'll need to set a reference in VBA to the
VBA Extensibility library. In the VBA editor, go to the Tools menu, choose
the References item, and put a check next to "Microsoft Visual Basic For
Applications Extensibility" library. This enables VBA to find the
definitions of these objects. If you are using Excel97, this library will
appear in the References list without a version number: "Microsoft Visual
Basic For Applications Extensibility". If you are using Excel 2000 or 2002,
it will appear with a version number: "Microsoft Visual Basic For
Applications Extensibility 5.3". It is very important that you reference
the proper library. If you reference the wrong library, you will receive
"Type Mismatch" errors. If you don't reference the extensibility library at
all, you will receive "User Defined Type Not Defined Error".
'<========================

 

[Guest]

Thanks Norman. Poor research on my part.

Hi Daniel,


Tools | Macros | Security | Trusted publishers tab
check "Trust Access to Visual Basic Project"


Chip Pearson's VBE prgramming page indicates:
'========================>
Before using these procedures, you'll need to set a reference in VBA to the
VBA Extensibility library. In the VBA editor, go to the Tools menu, choose
the References item, and put a check next to "Microsoft Visual Basic For
Applications Extensibility" library. This enables VBA to find the
definitions of these objects. If you are using Excel97, this library will
appear in the References list without a version number: "Microsoft Visual
Basic For Applications Extensibility". If you are using Excel 2000 or 2002,
it will appear with a version number: "Microsoft Visual Basic For
Applications Extensibility 5.3". It is very important that you reference
the proper library. If you reference the wrong library, you will receive
"Type Mismatch" errors. If you don't reference the extensibility library at
all, you will receive "User Defined Type Not Defined Error".
'<========================