[SOLVED] Error while Checking for Regular Expressions Library in VBA

The following macro is no longer working. I am getting a Run Time Error 1004
saying "Programmatic Access to Visual Basic Project not Trusted". It's
highlighting the line of code:

For Each ref In ThisWorkbook.VBProject.References

I have the Regular Expressions 5.5 clicked off. This was working fine in XL
2000 until I merged two XLAs together and started running it in XL 2003

Why is this?


Private Sub ExternalLinkChkBx_Click()
Dim ref As Object
Dim fRef As Boolean

If ExternalLinkChkBx = False Then
SelectAllChkBx = False
End If

If ExternalLinkChkBx = True Then
'Check to see if user has RegExp library
'installed. If not, prompt user to not
'choose this option.
For Each ref In ThisWorkbook.VBProject.References
If ref.Name = "VBScript_RegExp_55" Then
'Debug.Print ref.Name
'ThisWorkbook.VBProject.References.Remove ref
fRef = True
'MsgBox ("Regular Expressions 5.5 Enabled")
End If
Next
If Not fRef Then MsgBox ("You do not have the Regular Expressions
Library checked off in the Tools Menu. In order for this External Link Audit
to be completed, you need to reference this library. Go to to the VB Editor
and then to Tools/References. Scroll down to Microsoft VBScript Regular
Expressions 5.5 and click on it. Then run this audit again.")
If Not fRef Then ExternalLinkChkBx = False

End If
End Sub

ExcelMonkey,

Check your reference list for missing references.

For example, do you have a reference set to MS VBA Extensibility? It may be
a new version, so perhaps you have lost your reference to that - or to
another file.

HTH,
Bernie
MS Excel MVP


"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:CA7FCAF9-1477-478D-ABA4-F07DAF0F384B@microsoft.com...
> The following macro is no longer working. I am getting a Run Time Error
> 1004
> saying "Programmatic Access to Visual Basic Project not Trusted". It's
> highlighting the line of code:
>
> For Each ref In ThisWorkbook.VBProject.References
>
> I have the Regular Expressions 5.5 clicked off. This was working fine in
> XL
> 2000 until I merged two XLAs together and started running it in XL 2003
>
> Why is this?
>
>
> Private Sub ExternalLinkChkBx_Click()
> Dim ref As Object
> Dim fRef As Boolean
>
> If ExternalLinkChkBx = False Then
> SelectAllChkBx = False
> End If
>
> If ExternalLinkChkBx = True Then
> 'Check to see if user has RegExp library
> 'installed. If not, prompt user to not
> 'choose this option.
> For Each ref In ThisWorkbook.VBProject.References
> If ref.Name = "VBScript_RegExp_55" Then
> 'Debug.Print ref.Name
> 'ThisWorkbook.VBProject.References.Remove ref
> fRef = True
> 'MsgBox ("Regular Expressions 5.5 Enabled")
> End If
> Next
> If Not fRef Then MsgBox ("You do not have the Regular Expressions
> Library checked off in the Tools Menu. In order for this External Link
> Audit
> to be completed, you need to reference this library. Go to to the VB
> Editor
> and then to Tools/References. Scroll down to Microsoft VBScript Regular
> Expressions 5.5 and click on it. Then run this audit again.")
> If Not fRef Then ExternalLinkChkBx = False
>
> End If
> End Sub

in Tools=>Macros=>Security, have you elected to trust access to the Visual
Basic Project.

--
Regards,
Tom Ogilvy

"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:CA7FCAF9-1477-478D-ABA4-F07DAF0F384B@microsoft.com...
> The following macro is no longer working. I am getting a Run Time Error
1004
> saying "Programmatic Access to Visual Basic Project not Trusted". It's
> highlighting the line of code:
>
> For Each ref In ThisWorkbook.VBProject.References
>
> I have the Regular Expressions 5.5 clicked off. This was working fine in
XL
> 2000 until I merged two XLAs together and started running it in XL 2003
>
> Why is this?
>
>
> Private Sub ExternalLinkChkBx_Click()
> Dim ref As Object
> Dim fRef As Boolean
>
> If ExternalLinkChkBx = False Then
> SelectAllChkBx = False
> End If
>
> If ExternalLinkChkBx = True Then
> 'Check to see if user has RegExp library
> 'installed. If not, prompt user to not
> 'choose this option.
> For Each ref In ThisWorkbook.VBProject.References
> If ref.Name = "VBScript_RegExp_55" Then
> 'Debug.Print ref.Name
> 'ThisWorkbook.VBProject.References.Remove ref
> fRef = True
> 'MsgBox ("Regular Expressions 5.5 Enabled")
> End If
> Next
> If Not fRef Then MsgBox ("You do not have the Regular Expressions
> Library checked off in the Tools Menu. In order for this External Link
Audit
> to be completed, you need to reference this library. Go to to the VB
Editor
> and then to Tools/References. Scroll down to Microsoft VBScript Regular
> Expressions 5.5 and click on it. Then run this audit again.")
> If Not fRef Then ExternalLinkChkBx = False
>
> End If
> End Sub

Thanks Tom. I am assuming that this is a new option in XL 2003. I am also
assuming that I will now have to test for this prior to running this line of
code. I tried running the macro recorder but could not seem to record the
click of the "Trust access to the Visual". How do I do this in VBA?

Thanks




"Tom Ogilvy" wrote:

> in Tools=>Macros=>Security, have you elected to trust access to the Visual
> Basic Project.
>
> --
> Regards,
> Tom Ogilvy
>
> "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> news:CA7FCAF9-1477-478D-ABA4-F07DAF0F384B@microsoft.com...
> > The following macro is no longer working. I am getting a Run Time Error
> 1004
> > saying "Programmatic Access to Visual Basic Project not Trusted". It's
> > highlighting the line of code:
> >
> > For Each ref In ThisWorkbook.VBProject.References
> >
> > I have the Regular Expressions 5.5 clicked off. This was working fine in
> XL
> > 2000 until I merged two XLAs together and started running it in XL 2003
> >
> > Why is this?
> >
> >
> > Private Sub ExternalLinkChkBx_Click()
> > Dim ref As Object
> > Dim fRef As Boolean
> >
> > If ExternalLinkChkBx = False Then
> > SelectAllChkBx = False
> > End If
> >
> > If ExternalLinkChkBx = True Then
> > 'Check to see if user has RegExp library
> > 'installed. If not, prompt user to not
> > 'choose this option.
> > For Each ref In ThisWorkbook.VBProject.References
> > If ref.Name = "VBScript_RegExp_55" Then
> > 'Debug.Print ref.Name
> > 'ThisWorkbook.VBProject.References.Remove ref
> > fRef = True
> > 'MsgBox ("Regular Expressions 5.5 Enabled")
> > End If
> > Next
> > If Not fRef Then MsgBox ("You do not have the Regular Expressions
> > Library checked off in the Tools Menu. In order for this External Link
> Audit
> > to be completed, you need to reference this library. Go to to the VB
> Editor
> > and then to Tools/References. Scroll down to Microsoft VBScript Regular
> > Expressions 5.5 and click on it. Then run this audit again.")
> > If Not fRef Then ExternalLinkChkBx = False
> >
> > End If
> > End Sub
>
>
>

I think it was introduced in xl2002 - but don't recall for sure.

Recording won't work because it can't be set (or queried directly) with
code.

I posted a copy of a technique to test it posted previously by Chip Pearson.
(wrote it awhile ago, but forgot to sent it).

--
Regards,
Tom Ogilvy
"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:7FE67C9C-F6A8-4C40-B0BB-73A593F06E61@microsoft.com...
> Thanks Tom. I am assuming that this is a new option in XL 2003. I am
also
> assuming that I will now have to test for this prior to running this line
of
> code. I tried running the macro recorder but could not seem to record the
> click of the "Trust access to the Visual". How do I do this in VBA?
>
> Thanks
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > in Tools=>Macros=>Security, have you elected to trust access to the
Visual
> > Basic Project.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> > news:CA7FCAF9-1477-478D-ABA4-F07DAF0F384B@microsoft.com...
> > > The following macro is no longer working. I am getting a Run Time
Error
> > 1004
> > > saying "Programmatic Access to Visual Basic Project not Trusted".
It's
> > > highlighting the line of code:
> > >
> > > For Each ref In ThisWorkbook.VBProject.References
> > >
> > > I have the Regular Expressions 5.5 clicked off. This was working fine
in
> > XL
> > > 2000 until I merged two XLAs together and started running it in XL
2003
> > >
> > > Why is this?
> > >
> > >
> > > Private Sub ExternalLinkChkBx_Click()
> > > Dim ref As Object
> > > Dim fRef As Boolean
> > >
> > > If ExternalLinkChkBx = False Then
> > > SelectAllChkBx = False
> > > End If
> > >
> > > If ExternalLinkChkBx = True Then
> > > 'Check to see if user has RegExp library
> > > 'installed. If not, prompt user to not
> > > 'choose this option.
> > > For Each ref In ThisWorkbook.VBProject.References
> > > If ref.Name = "VBScript_RegExp_55" Then
> > > 'Debug.Print ref.Name
> > > 'ThisWorkbook.VBProject.References.Remove ref
> > > fRef = True
> > > 'MsgBox ("Regular Expressions 5.5 Enabled")
> > > End If
> > > Next
> > > If Not fRef Then MsgBox ("You do not have the Regular Expressions
> > > Library checked off in the Tools Menu. In order for this External
Link
> > Audit
> > > to be completed, you need to reference this library. Go to to the VB
> > Editor
> > > and then to Tools/References. Scroll down to Microsoft VBScript
Regular
> > > Expressions 5.5 and click on it. Then run this audit again.")
> > > If Not fRef Then ExternalLinkChkBx = False
> > >
> > > End If
> > > End Sub
> >
> >
> >

Chip Pearson posted this a short time ago in response to a related question:

Dim Ref As Object
On Error Resume Next
Set Ref = ThisWorkbook.VBProject.References("Excel")
If Ref Is Nothing Then
Debug.Print "No trust"
Else
Debug.Print "trust"
End If

This tests whether access to the VBA project has been enabled.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uVOJ8KRzFHA.3000@TK2MSFTNGP12.phx.gbl...
> in Tools=>Macros=>Security, have you elected to trust access to the Visual
> Basic Project.
>
> --
> Regards,
> Tom Ogilvy
>
> "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> news:CA7FCAF9-1477-478D-ABA4-F07DAF0F384B@microsoft.com...
> > The following macro is no longer working. I am getting a Run Time Error
> 1004
> > saying "Programmatic Access to Visual Basic Project not Trusted". It's
> > highlighting the line of code:
> >
> > For Each ref In ThisWorkbook.VBProject.References
> >
> > I have the Regular Expressions 5.5 clicked off. This was working fine
in
> XL
> > 2000 until I merged two XLAs together and started running it in XL 2003
> >
> > Why is this?
> >
> >
> > Private Sub ExternalLinkChkBx_Click()
> > Dim ref As Object
> > Dim fRef As Boolean
> >
> > If ExternalLinkChkBx = False Then
> > SelectAllChkBx = False
> > End If
> >
> > If ExternalLinkChkBx = True Then
> > 'Check to see if user has RegExp library
> > 'installed. If not, prompt user to not
> > 'choose this option.
> > For Each ref In ThisWorkbook.VBProject.References
> > If ref.Name = "VBScript_RegExp_55" Then
> > 'Debug.Print ref.Name
> > 'ThisWorkbook.VBProject.References.Remove ref
> > fRef = True
> > 'MsgBox ("Regular Expressions 5.5 Enabled")
> > End If
> > Next
> > If Not fRef Then MsgBox ("You do not have the Regular Expressions
> > Library checked off in the Tools Menu. In order for this External Link
> Audit
> > to be completed, you need to reference this library. Go to to the VB
> Editor
> > and then to Tools/References. Scroll down to Microsoft VBScript Regular
> > Expressions 5.5 and click on it. Then run this audit again.")
> > If Not fRef Then ExternalLinkChkBx = False
> >
> > End If
> > End Sub
>
>