Macro Still Won't Run in Excel 2003

[Guest]

I work with Mark who posted the following last week:

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"

After making sure we checked the appropriate resource under the tools menu
in the VBA editor, the macro did in fact run on my desk top computer.
However, checking this box on my lap top computer does not resolve the
problem. I have verified that both machines have the same resources checked
off, same product ID, version, and service pack. Any thoughts on why this
would work in some cases but not others?

Tom

 

[Tom Ogilvy]

Maybe you have 3rd party software like Norton which won't let it run since
it has code that modifies code.

 

[CBrausa]

Tom-
You gave me a suggestion on my Nesting problem yesterday. This is the
formula I used. Unfortunately I don't know how to do the formula you
suggested.

=IF(($C17>30),VLOOKUP($H17,DEPT3138,3,FALSE),IF($C17>25,VLOOKUP($H17,DEPT2630,3,FALSE),IF($C17>20,VLOOKUP($H17,DEPT2125,3,FALSE),IF($C17>15,VLOOKUP($H17,DEPT1620,3,FALSE),IF($C17>10,VLOOKUP($H17,DEPT1115,3,FALSE),IF($C17>5,VLOOKUP($H17,DEPT610,3,FALSE),VLOOKUP($H17,DEPT15,3,FALSE)))))))

I appreciated your help I'll get out my Excel Formula book and see if I
can make it work. Thanks again. CBrausa

 

[Tom Ogilvy]

Well, you know what you mission and what you data is an I don't. If you can
build 7 tables, then why can't you build 1 with all 38 departments.

Anyway, good luck.

Anyway, for you current endeavor, You could use something like


=Vlookup($H17,Choose(Trunc(C17)/5+1,Dept15,Dept610,Dept1115,Dept1620,Dept212
5,Dept2630,Dept3138),3,False)

I haven't checked the math, so it might require some slight tinkering.

 

[CBrausa]

Tom-
I have approx. 130,000 rows of information and there are only, as you
know, 65,536 rows per sheet. Out of 13 columns of information per
Dept. I need to pull info from 7 of the columns over to the main
worksheet. It needs to look first at the Dept. number (C17) then at
the SKU that then leads to the information in that row and picks up the
information needed. It's works, except for the fact that it's too much
information and I get the error message, up to the point of adding the
seperate worksheets with the 5 +/- depts. on it. It also worked better
until I linked the seperate worksheets to the original Dept. info sheet,
which is updated monthly. If I knew what formula to use so it would
look at all 38 sheets that would be great.