Danny
09-09-2009, 08:25 PM
Greetings,
I am trying to use a code by Ken Puls that I found in the KB (http://www.vbaexpress.com/kb/getarticle.php?kb_id=267)
I have put this code in workbook module to run when the workbook opens. The code works perfectly if the TRUST ACCESS TO VISUAL BASIC PROJECT, in the Macro security settings, is checked, but if this option is not checked Excel will freeze up, and i have had to use the task manager to end the program.
So, my first question is, am i doing something wrong? And if not, is there any way to test if access to visual basic project is allowed before this code runs? Several people will use this file, but not all of them will need to reference the Word library, and they will not have the trust access to vb project option checked.
Here is the code:
Private Sub Workbook_Open()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library
Dim strGUID As String, theRef As Variant, i As Long
'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"
'Set to continue in case of error
On Error Resume Next
'Remove any missing references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
End Sub
I am trying to use a code by Ken Puls that I found in the KB (http://www.vbaexpress.com/kb/getarticle.php?kb_id=267)
I have put this code in workbook module to run when the workbook opens. The code works perfectly if the TRUST ACCESS TO VISUAL BASIC PROJECT, in the Macro security settings, is checked, but if this option is not checked Excel will freeze up, and i have had to use the task manager to end the program.
So, my first question is, am i doing something wrong? And if not, is there any way to test if access to visual basic project is allowed before this code runs? Several people will use this file, but not all of them will need to reference the Word library, and they will not have the trust access to vb project option checked.
Here is the code:
Private Sub Workbook_Open()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library
Dim strGUID As String, theRef As Variant, i As Long
'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"
'Set to continue in case of error
On Error Resume Next
'Remove any missing references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
End Sub