Macro Won't Run in Excel 2003

[Guest]

The following code runs in Excel 2002 but not in Excel 2003.

We get the error: "Run Time Error '1004' Programmatic access to Visual Basic
Project is not trusted". It gets hung-up on the "For Each" line in the
following code:

Dim vbModule As Object
For Each vbModule In appWB.ActiveWorkbook.VBProject.VBComponents
If vbModule.Type = 1 Then ' "1" is a module
appWB.ActiveWorkbook.VBProject.VBComponents.Remove vbModule
End If
Next vbModule

We have checked the "Trust Access to Visual Basic Project" check box in the
Tools>Macro>Security>Trusted Sources tab (at the bottom), but to no avail.

Any Ideas?

A poster in this newsgroup suggested that we check the registry, but not
sure how to do this. Instruction in this area would be appreciated.

Thank you, Mark

 

[Bunter_22]

Hi Mark,

Have you set a reference to the "Microsoft Visual Basic for
Applications Extensibility 5.3" with in the VBE? This you will need
when changing VBmodules etc within code.

James

 

[Guest]

I have a glance at your macro. I think the "appWB" can be removed.
See the modified code below:

Dim vbModule As Object
For Each vbModule In ActiveWorkbook.VBProject.VBComponents
If vbModule.Type = 1 Then ' "1" is a module
ActiveWorkbook.VBProject.VBComponents.Remove vbModule
End If
Next vbModule

However, I would like to remind you that, your macro will potentially remove
the module in which the code resides...

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Guest]

James -

It worked!!!! I was getting worried.

You know this reference was unchecked for Excel 2002 but the code to ran.
Very strange.

Thank you!!!!

Mark

 

[Bunter_22]

I can't explain excel 2002 but I know that I have had to check this
when adding/removing modules with in code. Glad to hear it was that
simple!

James