Hi,
I am working with a form in Excel, and I've been trying to get the
calendar control (11) to fire when only the month and year are
completed, but I cannot find a way.
Currently, I have this code behind the calendar (which comes up from a
button on the form):

=======
Private Sub calendar1_Click()

Calendar1.Day = 1

frmCourseBooking.txtDateTaken.Value = frmCal.Calendar1.Value

Unload frmCal
End Sub
==========
This does work, but it only will work one time: if the "parent" form is
left open and the calendar launch button is clicked again, the calendar
does come up, but the first day of the month isn't selected after the
first time. Basically, I'm just trying to get around having to select
the day by having it in the code. Any help is greatly appreciated.

Thanks,
Louis

Hi

If I understand you correct?

Try this
Calendar1.Value = DateSerial(Year(Date), Month(Date), 1)


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ll" wrote in message ups.com...
Hi,
I am working with a form in Excel, and I've been trying to get the
calendar control (11) to fire when only the month and year are
completed, but I cannot find a way.
Currently, I have this code behind the calendar (which comes up from a
button on the form):

=======
Private Sub calendar1_Click()

Calendar1.Day = 1

frmCourseBooking.txtDateTaken.Value = frmCal.Calendar1.Value

Unload frmCal
End Sub
==========
This does work, but it only will work one time: if the "parent" form is
left open and the calendar launch button is clicked again, the calendar
does come up, but the first day of the month isn't selected after the
first time. Basically, I'm just trying to get around having to select
the day by having it in the code. Any help is greatly appreciated.

Thanks,
Louis

Thanks,

I am really looking for a way to make the calendar only "need" the
month and year - without having to manually click on the day in order
to fire the script. I've tried doing an "on change" so that when the
month was changed, the date would automatically be placed in a box on
the form, but that didn't work.

I am trying to put the calendar into the form itself, rather than
having it on a secondary form. I have hidden the days, as they are not
needed. I have one submit button that submits a course name, category,
and (hopefully) the date to a spreadsheet from this form. The problem
is, the date will not populate the spreadsheet without having a click
event from the days - I wonder if my only option is to set up another
"OK" button next to the calendar selection in order to fire the date?

Thanks again

You can use this maybe

Private Sub Calendar1_NewMonth()
End Sub

Private Sub Calendar1_NewYear()
End Sub

See also
http://www.rondebruin.nl/calendar.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ll" wrote in message oups.com...
Thanks,

I am really looking for a way to make the calendar only "need" the
month and year - without having to manually click on the day in order
to fire the script. I've tried doing an "on change" so that when the
month was changed, the date would automatically be placed in a box on
the form, but that didn't work.

I am trying to put the calendar into the form itself, rather than
having it on a secondary form. I have hidden the days, as they are not
needed. I have one submit button that submits a course name, category,
and (hopefully) the date to a spreadsheet from this form. The problem
is, the date will not populate the spreadsheet without having a click
event from the days - I wonder if my only option is to set up another
"OK" button next to the calendar selection in order to fire the date?

Thanks again

Btw: This are events of the control

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
You can use this maybe

Private Sub Calendar1_NewMonth()
End Sub

Private Sub Calendar1_NewYear()
End Sub

See also
http://www.rondebruin.nl/calendar.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ll" wrote in message oups.com...
Thanks,

I am really looking for a way to make the calendar only "need" the
month and year - without having to manually click on the day in order
to fire the script. I've tried doing an "on change" so that when the
month was changed, the date would automatically be placed in a box on
the form, but that didn't work.

I am trying to put the calendar into the form itself, rather than
having it on a secondary form. I have hidden the days, as they are not
needed. I have one submit button that submits a course name, category,
and (hopefully) the date to a spreadsheet from this form. The problem
is, the date will not populate the spreadsheet without having a click
event from the days - I wonder if my only option is to set up another
"OK" button next to the calendar selection in order to fire the date?

Thanks again

Thanks Ron,
It works now, but it will only enter the date once - each subsequent
date or year change will put an empty value ("") into the cell. Is
there perhaps a trap for this? I've thought about using .clear, but
apparently it can't be used with the calendar.

In order to change the date again, I have to close and re-open the
form.

Thanks again