using a calendar control

[natanz]

I am confused about the calendar control. I used the tools\additional
controls menu to add calendar control 9.0 to my vba project.

I am designing a userform, that has a combobox, where a date will be
input. The default value of the combobox is set to todays date in the
initialize event of the userform as follows:

ComboBox1.Value = CDate(FormatDateTime(Now, vbShortDate))

on the dropButtonclick event of the combobox, i use the following code
to bring up a second userform (calendar) which has a calendar control
on it, and a cancel button:

Private Sub ComboBox1_DropButtonClick()
Calendar.Show
End Sub

so far so good. It works as i would expect.
But...
I want to transfer the selection from this calendar control back to the
combobox.

I am using this code:

Private Sub Calendar1_Click()
LogInNew.ComboBox1.Value = Calendar1.Value
LogInNew.ListBox1.SetFocus
Unload Me
End Sub

now this isn't perfect, because it isn't limited to selection clicks.
ie if you click anywhere on the control, the calendar unloads. I can
live with that. the bigger problem is that it only works about half
the time. sometimes the value in combobox1 changes, and the focus is
re-set to the next control, but sometimes combobox1 doesn't change, and
the control is stuck sort of open.

Is there some better way to do what i am trying to do? Is there a
better event to use?
also, another important consideration is, am i going to have problems
distributing this spreadsheet, which i am designing as a template for
many users in my office?

any help would be appreciated.

 

[tony h]

I will have a think about the problem but on the general point of
distributing the spreadsheet I do have a concern.
I avoid using the calendar control because there used to be a problem
of compatability between the various calendar control versions and also
between the VBA and VB versions. I strongly suggest that you test this
out before commiting to using it.

regards,

 

[Mike Fogleman]

This site may give you some tips: http://www.fontstuff.com/vba/vbatut07.htm
In the example the Calendar value is transferred to a worksheet cell, but
you could easily modify it to work with the ComboBox.

Mike F

 

[natanz]

thanks for this. i actually started with the tutorial from fontstuff.
my problem is that i think the control is just not working very well.
when i select a new date, it just seems like it doesn't work. Is there
an alternatives to using this control, that will work better?