Programmatic Access to Visual Basic Project is not Trusted

[Guest]

Hello,

I have a long program written in Excel 97 and I have transferred to Excel 2000 when I run it, it gave me the following error:

Run time error '1004'
Programmatic Access to Visual Basic Project is not Trusted

due to the section of the codes below:

'--------------------Prepare Modules in New Workbook--------------------
'Copy Modules
ModName = ThisWorkbook.Path & "\Merit Worksheet Code.txt"
For Each VBComponent In ThisWorkbook.VBProject.VBComponents
'Perform export and import module other than Excel objects
If VBComponent.Type <> vbext_ct_Document Then

'Export Module> Import Module> Delete Module Code File
VBComponent.Export ModName
NewBook.VBProject.VBComponents.Import ModName
Kill ModName
End If

Next VBComponent
'--------------------------------------------------------------------------

It worked fine in the Excel 97 why isn't it working for on my Excel 2000? Any hint would be greatly appreciated. Thanks.

Anson

 

[Rob Bovey]

Hi Anson,

You must mean Excel 2002 or 2003. A new macro security setting was added
to Excel 2002 that you must "approve" in order to perform almost any VBA
manipulation of objects that are part of the VBProject of a workbook. Choose
Tools/Macro/Security from the Excel menu, select the Trusted Sources tab and
put a check in the checkbox called "Trust Access to Visual Basic Project".

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hello,

I have a long program written in Excel 97 and I have transferred to Excel

2000 when I run it, it gave me the following error:

Run time error '1004'
Programmatic Access to Visual Basic Project is not Trusted

due to the section of the codes below:

'--------------------Prepare Modules in New Workbook--------------------
'Copy Modules
ModName = ThisWorkbook.Path & "\Merit Worksheet Code.txt"
For Each VBComponent In ThisWorkbook.VBProject.VBComponents
'Perform export and import module other than Excel objects
If VBComponent.Type <> vbext_ct_Document Then

'Export Module> Import Module> Delete Module Code File
VBComponent.Export ModName
NewBook.VBProject.VBComponents.Import ModName
Kill ModName
End If

Next VBComponent
'--------------------------------------------------------------------------

It worked fine in the Excel 97 why isn't it working for on my Excel 2000?

Any hint would be greatly appreciated. Thanks.

 

[Ron de Bruin]

Hi Anson

You can add this code to the workbook.
http://www.j-walk.com/ss/excel/tips/tip96.htm

 

[Guest]

Thank you both for your help! (You are right my version is indeed 2002)