Is there a way to have VBA check to see if there is a reference and, if not, set it on an
open event?

I need to set a reference to the "Microsoft Shell Controls and Automation" object if it's
not already set.
(it will save me having to guide someone over the phone-albeit not a long trip!)

xl2kPro
--
Regards;
Rob
------------------------------------------------------------------------

Hi Rob

this code will set the reference for the activeworkbook.

Sub addReference()

Const REF_FULL_NAME As String = "C:\Windows\System32\Shell32.dll"
Const REF_SHORT_NAME As String = "Shell32"

Dim objRef As Object

On Error Resume Next '//ignore the error if it doesn't exist
Set objRef = ActiveWorkbook.VBProject.References.Item(REF_SHORT _NAME)
On Error GoTo 0

If objRef Is Nothing Then
ActiveWorkbook.VBProject.References.AddFromFile (REF_FULL_NAME)
Else
'already set so do nothing
End If

End Sub

HTH

Barry

"RWN" wrote:

Is there a way to have VBA check to see if there is a reference and, if not, set it on an
open event?

I need to set a reference to the "Microsoft Shell Controls and Automation" object if it's
not already set.
(it will save me having to guide someone over the phone-albeit not a long trip!)

xl2kPro
--
Regards;
Rob
------------------------------------------------------------------------

Barry;

Looks wonderful, I'll try it tomorrow.

Thanks.

--
Regards;
Rob
------------------------------------------------------------------------
"Barry" wrote in message
...
Hi Rob

this code will set the reference for the activeworkbook.

Sub addReference()

Const REF_FULL_NAME As String = "C:\Windows\System32\Shell32.dll"
Const REF_SHORT_NAME As String = "Shell32"

Dim objRef As Object

On Error Resume Next '//ignore the error if it doesn't exist
Set objRef = ActiveWorkbook.VBProject.References.Item(REF_SHORT _NAME)
On Error GoTo 0

If objRef Is Nothing Then
ActiveWorkbook.VBProject.References.AddFromFile (REF_FULL_NAME)
Else
'already set so do nothing
End If

End Sub

HTH

Barry

"RWN" wrote:

Is there a way to have VBA check to see if there is a reference and, if not, set it on
an
open event?

I need to set a reference to the "Microsoft Shell Controls and Automation" object if
it's
not already set.
(it will save me having to guide someone over the phone-albeit not a long trip!)

xl2kPro
--
Regards;
Rob
------------------------------------------------------------------------