Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all modules and forms from workbook
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").Nam e = "Module2" returns this error: "Programmatic access to Visual Basic Project is not trusted" And this procedu 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all modules and forms from workbook
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". '<======================== --- Regards, Norman "Daniel Bonallack" wrote in message ... 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").Nam e = "Module2" returns this error: "Programmatic access to Visual Basic Project is not trusted" And this procedu 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all modules and forms from workbook
Thanks Norman. Poor research on my part.
"Norman Jones" wrote: 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". '<======================== --- Regards, Norman "Daniel Bonallack" wrote in message ... 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").Nam e = "Module2" returns this error: "Programmatic access to Visual Basic Project is not trusted" And this procedu 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
Automatically Delete WorkBook 2 modules by using Workbook 1 module | Excel Programming | |||
Forms and Modules | Excel Programming | |||
Modules and Forms II | Excel Programming | |||
Modules and Forms | Excel Programming |