Using a Date time picker in a VBA form

[tysop]

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 Tools>References 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.

 

[Brian]

I have the same issue. I am running excel 2003 SP2 in XP. I have no
problems using the file, but others in the office get the compile error
message, however, on at least one user's machine, the "missing" reference
refers to a MS project calendar control.
Thanks

 

[Gary L Brown]

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 L Brown]

To get a list of the references and their GUID, use the following procedure...

'/==========================================/
Public Sub ReferenceList()
Dim objRef As Object
Dim strAnswer As String

For Each objRef In ActiveWorkbook.VBProject.References
strAnswer = strAnswer & "Name: " & objRef.Name & vbCr & _
"Description: " & objRef.Description & vbCr & _
"Location: " & objRef.fullpath & vbCr & _
"GUID: " & objRef.GUID & vbCr & vbCr
Next objRef

MsgBox strAnswer

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

HTH,

 

[Gary L Brown]

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,