Macro Won't Run in Excel 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
 
Back
Top