Determining If Access To The VB Project Is Allowed
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.'", _ vbCritical 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
Browse Tips by Category
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