Excel 2007 and Trusted Publishers

[ExcelMonkey]

I have an xla Addin with a line of code as follows below. This was written
to accomodate Excel 2003.

I know in Excel 2007 I have to go to Office Button/Excel Options/Trust
Center/Trust Center Settings. However I am not sure what do when I get here.
I have tried adding the Addin as a Trust location and allowing its
subfolders. However this does not work. I keep getting the error message.

Set Ref1 = ThisWorkbook.VBProject.References("Excel")
If Ref1 Is Nothing Then
MsgBox ("Note that you do not have Trust Access to Visual Basic
clicked on under your security tab. Go to Tools/Macros/Security/Trusted
Publishers and click this on. Then re-run this audit option.")
Unload MainUserForm
Exit Sub
Else
'do something
End if

 

[Ron de Bruin]

Hi ExcelMonkey

Do you have the same problem when you use this

On Error Resume Next
Set VBP = ThisWorkbook.VBProject
If Err.number <> 0 Then
MsgBox "Your security settings do not allow this procedure to run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Office Button > Excel Options...Trust Center." & vbCrLf _
& " 2. Click on 'Trust Center Settings' " & vbCrLf _
& " 2. Click on 'Macro Settings' " & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to the VBA project object model.'", _
vbCritical
ThisWorkbook.Close False
Exit Sub
End If

 

[ExcelMonkey]

Thanks Ron, that worked. I was obviously looking in the wrong area. Just
out of curiosity what does the Trusted Location options do?

Thanks

EM

 

[Ron de Bruin]

Hi ExcelMonkey

If you Add or Delete VBA code for example with code this setting must be checked.

See Chip's site for more information
http://www.cpearson.com/excel/vbe.aspx