Determining If Access To The VB Project Is Allowed

Category: General VBA | [Item URL]

As you may know, you can write VBA code that manipulates Visual Basic project components. For example, your code can insert and delete modules, or even create a UserForm on the fly. If your Excel application modifies the Visual Basic project, you need to be aware of a security setting introduced in Excel 2002: Trust access to Visual Basic Project.

This setting is changed in Excel's Security dialog box, accessible via the Tools - Macro - Security command.

By default, this setting is turned off. And, of course, it cannot be changed via code. This means that VBA code that attempts to access the VB project will fail, and the user will be presented with an unfriendly message from Excel.

Unfortunately, there is no direct way to programmatically determine the value of this setting. The only way to do it is to trap an error and then take appropriate action. The VBA code listed below does just that.

' Make sure access to the VBProject is allowed
Dim VBP As Object ' as VBProject
If Val(Application.Version) >= 10 Then
    On Error Resume Next
    Set VBP = ActiveWorkbook.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. Select Tools - Macro - Security." & vbCrLf _
          & " 2. Click the 'Trusted Sources' tab" & vbCrLf _
          & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _
        Exit Sub
    End If
End If

This code first checks the Excel version number. If Excel 2002 or later is in use, it attempts to create an object variable (VBP). If an error occurs, then the user is presented with instructions on how to change the setting (see below) and the procedure ends.

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy