The reason I step backwards thru the loop is that I want the highest/latest
version. Once the procedure recognizes the reference, any lower versions
will produce an error and be ignored.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Gary L Brown" wrote:

Try calling this routine from your code...

'/============================================/
Public Sub AddDtPickerReference()
Dim x As Integer, y As Integer
Dim varRef As Variant

On Error Resume Next

For x = 20 To 0 Step -1 'Major version
For y = 20 To 0 Step -1 'Minor version
varRef = _
ActiveWorkbook.VBProject.References. _
AddFromGuid("{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}", x, y)
Next y
Next x

End Sub
'/============================================/

Not knowing exactly where the file may be located on your client's machine
(in my case, it's at C:\WINNT\system32\mscomct2.ocx), but knowing the GUID
({86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}), you can strong-arm the reference.
Of course, if the client doesn't have the file at all, nothing will work.
I also never know exactly what version of the file the client has. In my
case, the Major version (x) is 2 and the Minor version (y) is 0. So I use a
For Loop to go thru 20 possiblities of major and minor versions. Why 20?
Why not :O.
BTW, my DTPicker is called 'Microsoft Windows Common Controls-2 6.0 (SP4)'.

What I would do is to put this routine in a module then put the following
line in the ThisWorkbook.Workbook_Open() routine...

Call AddDtPickerReference


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"tysop" wrote:

I'm trying to create an input form that contains a Date and time picker
(DTPicker) to be distributed to different versions of Excel. I am using
excel 2000, however when I open it in Excel XP I get the following error
message:
Compile Error
Can't find project or library

And in ToolsReferences I get Missing: Microsoft Windows Common
Controls-2.6.0(SP4)

Is there an easy alternative instead of the DTPicker or some code to fix this?
I don't have control over other users having any particular add-ins/updates
applied unfortunately, or even what version of Excel or what install options
were chosen.

Any help would be much appreciated.