Date Time Picker control is not working on every system

[Sachin2k6]

Hi guys
I'm a VBA Developer. I developed some VBA programs (See & download at www.excelapps.webs.com). Some of these programs uses a window control named Windows date-time Picker control. Library of this control is ‘mscomct2.ocx’ default directory for which is c:\windows\system32\ on 32 bit system & c:\windows\syswow64 on 64 bit system.

Problem is that this control is not available on all systems. On most of the system it works after copying its .ocx file to the default directory and then registering it through the command line using Regsvr32 c:\windows\system32\mscomct2.ocx but on some systems (64 bit as well as 32 bit) this registration process failed giving the message ‘The module was loaded but call to DllRegisterServer failed. Error code 0x8002801c.’ In that case control do not work on that system.

I have to find out the reason for this.
Can anyone help me??

 

[ChrisM]

I don't know why you are getting that registration error, but I'd like to add that I gave up trying to use non-standard controls like this for this exact reason. You'll never be able to plan for all the different types of systems out there, I find it much safer to only use the built in controls. You can actually build your own date picker out of labels and listboxes, and it will work on all systems. I have one I built in a class module that works exactly like the MS one.

 

[Sachin2k6]

I don't know why you are getting that registration error, but I'd like to add that I gave up trying to use non-standard controls like this for this exact reason. You'll never be able to plan for all the different types of systems out there, I find it much safer to only use the built in controls. You can actually build your own date picker out of labels and listboxes, and it will work on all systems. I have one I built in a class module that works exactly like the MS one.

Can you please share it?

 

[ChrisM]

There is a form and user module, so it can't all be included here. I've posted the file on dropbox:

https://www.dropbox.com/s/btl8jbryp3ov2be/calendar3.xls

Here are the steps to get this working in your file:

1. Export the class module out of my file and in to your file
2. If you already have a code Module in your file, copy the two lines from my Module1 to yours, otherwise create a new Module with my two lines
3. Note the code in Sheet1. This is where you launch the calendar. Here it's in response to a button click, but change to whatever you need. I normally use it in a userform.
4. Open the class module and look at the label click event. At the end all that it will do is give you a message box with the selected date. This is just my development version, so it doesn't really do anything. In the actual version, you have to track which cell or user control called the calendar, and then you need to return the value back to it.

So here's what I do for my userform:
4 additional variables in the global module:

Public sCallingControl As String
Public sCallingForm As UserForm
Public xX As Single 'where you want the calendar to appear
Public xY As Single

Pretty self-explanatory, you just fill in all those values in the event that calls the calendar. Here's an example from a userform text box click:

Private Sub txtExpCloseDate_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal y As Single)
    Set sCallingForm = frmMain
    sCallingControl = "txtExpCloseDate"
    xX = X
    xY = y
    With txtExpCloseDate
        If .Text <> "" Then 'if existing date in box, set current date to that
            If IsDate(.Text) Then
                CurrDate = .Text
                frmCal.Calendar_Change
            Else
                CurrDate = Date
            End If
        Else
            CurrDate = Date
        End If
        
        xX = frmMain.Left + .Left + .Width + 15
        xY = frmMain.Top + .Top
    End With
    frmCal.Show
End Sub

note that xX and xY are used to place the calendar relative to where the textbox is. You'll find these values used in the frmCal code.

Then, once a label is clicked, here's the code from my class module that passes it back to the calling control:

Private Sub Lbl_Click()

    Dim i As Integer
    For i = 1 To 42 'clear any highlighted squares
        frmCal.Controls(aLbls(i)).BorderColor = &H80000006
    Next i

    Lbl.BorderColor = &HFF&
    CurrDate = Lbl.ControlTipText
    frmCal.Repaint
    sCallingForm(sCallingControl).Text = Lbl.ControlTipText
    Set sCallingForm = Nothing
    frmCal.Hide
End Sub

It's not quite plug and play, but once you get all the code set up it only takes 5 seconds to add this for each additional control/cell that needs a calendar.

 

[Sachin2k6]

Many thanks chris.
I'm looking your code, will give you feedback soon.

 

[ChrisM]

Oops, just realized I didn't tell you to export the calendar form from my file to yours as well! Duh!! You won't get far without that! So a new step 1a, export frmCal in to your file.

 

[Sachin2k6]

Thanks chris.
One little problem is that the calendar form is not displaying at specific location.
It should be displayed at left bottom corner of the callee object or at the left bottom corner of the cell in a worksheet.

 

[ChrisM]

Sorry about that, the file I posted was just what I used to initially build the process. I changed it a bit once I actually implemented it in a working spreadsheet.

1. Declare the xX and xY variables as you see in my post #4 above.
2.Then in the event you want to launch the calendar, set those x and y values, as I did in my mouse_down sub above.
3. Add this sub to the calendar form:

Private Sub UserForm_Activate()
    frmCal.Left = xX
    frmCal.Top = xY
End Sub

What event are you using to launch the calendar? If you need it next to the active cell you can use:

xX = Selection.Left

or something like that, though I think that references from the top left cell and not necessarily the top left of the whole screen, so you might need to put an additional offset in there.