Checking if 'Trust Access to Visual Basic Project' is ticked

[George J]

I have a macro that seems to work okay for older versions of Excel - I use Office XP (2002)
Can someone tell me if this will work for newer versions (2007 / 2010) please?
And if not, can anyone suggest how check this?

KB1017 - Trust access to VBA project

Function VBATrusted() As Boolean
On Error Resume Next
VBATrusted = (Application.VBE.VBProjects.Count) > 0
Exit Function
End Function

Private Sub Workbook_Open()
If Not VBATrustedAccess() Then
    MsgBox "No Access to VB Project" & vbLf & _
      "Please allow access in Trusted Sources" & vbLf & _
      "(Tools > Macro > Security > Trusted Source Tab)"
End If
End Sub

I was thinking that i should probably have
VBATrusted = -1
as the third line so that it is negative unless the tick is there. No way of checking older versions right now though.

All help appreciated.

 

[Domenic]

It looks like there's no need to exit the function, and the function being called in the Workbook open event should be VBATrusted, not VBATrustedAccess...

[FONT=Courier New][COLOR=darkblue]Function[/COLOR] VBATrusted() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    VBATrusted = (Application.VBE.VBProjects.Count) > 0
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]
[COLOR=darkblue]Private Sub Workbook_Open()[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] VBATrusted() [COLOR=darkblue]Then[/COLOR]
        MsgBox "No Access to VB Project" & vbLf & _
          "Please allow access in Trusted Sources" & vbLf & _
          "(Tools > Macro > Security > Trusted Source Tab)"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
[/FONT]

...which works in both Excel 2007 and 2010. By the way, the settings can be accessed as follows...

For Excel 2007...

Office Button > Excel Options > Trust Center > Trust Center Settings > Macro Settings > Trust Access...

For Excel 2010

File > Options > Trust Center > Trust Center Settings > Macro Settings > Trust Access...

 

[George J]

Thanks very much for that Domenic. Can't think why i didn't notice the Exit Function line.

The user i had sent the workbook to told me they didn't see any popup (the Function was being called correctly at that time). I guess some people are just too used to closing down error messages. I might amend the message to Yes/No - 'Do you require instructions on how to do this?' Yes will take them to the KB1017 page. If they still can't get it to work for them, then that's their problem.

I appreciate your reply to this query - i was getting worried that the newer versions could only be checked with 'more modern' code.

thanks again
George

 

[Domenic]

Thanks very much for that Domenic.

You're very welcome!

If they still can't get it to work for them, then that's their problem.

Macros need to be enabled, though.