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

 

[careywalker]

Record a macro and insert the date control while recording the macro. That will give you the vba code you need to add the date control.

 

[Vladislav]

Doesn't work.

 

[careywalker]

Ok, I can see now that you were asking about XL2K3. What I was saying to do works in Excel2007. When you say it doesn't work, do you mean that when you try to record the macro it doesn't capture the you adding the date control? Or does it capture the macro but when you try to use what has been recorded it doesn't insert the date control?

 

[Vladislav]

If it works in 2k7, could you please post the code generated, thanks.

 

[careywalker]

If it works in 2k7, could you please post the code generated, thanks.

Sure, here you go. Note that you might have to change the name of the control to the control name that is available in XL2K3.

Sub insertDatePicker()
'
' insertDatePicker Macro
'

ActiveSheet.OLEObjects.Add(ClassType:="MSComCtl2.DTPicker.2", Link:=False, _
DisplayAsIcon:=False, Left:=192.75, Top:=44.25, Width:=95.25, Height _
:=16.5).Select
End Sub

 

[Vladislav]

Hi Carey,

that worked with a little tweak for the userform, thanks a lot!!

cheers,
Vlad

 

[careywalker]

Your welcome. Least I could do after a 15-6 result

 

[John_H]

Hi Vladislav,
Would you care to post the tweak that you used for the userform datepicker?
Trying to load the DatePicker onto a userform at design time is driving me crazy
Regards
John

 

[Norie]

John

Just change ActiveSheet.OLEObjects to Me.Controls.