Create DatePicker on the fly in XL2K3

[Vladislav]

Hi everyone,

I'm looking for a way to create a date picker control in a userform on the fly in XL2K3. My preference is to use the .add method and a progID like to labels, textboxes, etc.. However, I don't know the date picker progID or whether this can be done that way.

It is possible to create date picker at design time, hide and then show when needed, but I'd rather not do that if I can avoid it.

Your help appreciated!

cheers,
Vlad

 

[John_H]

Hi Norie,
Thanks for responding.
Unfortunately that did not work, it showed as a failure to recognnise 'ClassType'.
Regards
John

 

[Norie]

John

Do you have the DTPicker installed?

By the way, why do you want to add? Couldn't you just have it on the form and hidden, or even on another form?

 

[John_H]

John

Do you have the DTPicker installed?

By the way, why do you want to add? Couldn't you just have it on the form and hidden, or even on another form?

Hi Norie,
Yes, I have DTPicker installed, referenced and registered and it does work when loaded onto a form manually.
The program will be used by different users under varying circumstances that require differing Form layout, and so needs to be built on the fly.
Regards
John

 

[John_H]

For anyone interested, the solution is:
Sub DesignTimeDatePicker()
'Adds a DatePicker at design time

Dim dtpCal As DTPicker

Set dtpCal = ThisWorkbook.VBProject.VBComponents("frmDEntry").Designer.Controls.Add("MSComCtl2.DTPicker.2")
With dtpCal
.Name = "dptTransDate2"
.Format = dtpLongDate
.Left = 90
.Width = 192
.Top = 126
.Height = 18
End With
End Sub

With thanks to Microsoft Help Desk