Using a Date time picker in a VBA form

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.

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

"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 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.
>
>

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
gary_brown@ge_NOSPAM.com
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 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.
>
>

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 Brown
gary_brown@ge_NOSPAM.com
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 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.
>
>

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
gary_brown@ge_NOSPAM.com
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
> gary_brown@ge_NOSPAM.com
> 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 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.
> >
> >