Date Picker
 

Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

Yes. First off, do you have the Developer Tab showing on your Ribbon? If
not, click the Office Button (large round icon in upper left corner of
Excel), click the Excel Options button at the bottom of the dialog that
displays, click the Popular item in the left-hand list and put a check mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click the
Developer Tab and then click the Insert icon on the Controls tab, then click
the "More Controls" icon in the bottom right corner of the ActiveX Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the list,
select it and click the OK button and draw the control onto the worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

If you not see the control in the list that Rick suggested you can try this
http://www.rondebruin.nl/calendar.htm

Also possible that you not have it (read the info on the page)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Rick Rothstein" wrote in message ...
Yes. First off, do you have the Developer Tab showing on your Ribbon? If
not, click the Office Button (large round icon in upper left corner of
Excel), click the Excel Options button at the bottom of the dialog that
displays, click the Popular item in the left-hand list and put a check mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click the
Developer Tab and then click the Insert icon on the Controls tab, then click
the "More Controls" icon in the bottom right corner of the ActiveX Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the list,
select it and click the OK button and draw the control onto the worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

Thanks Rick, I have it on my sheet now, but how do I make it work?

"Rick Rothstein" wrote:

Yes. First off, do you have the Developer Tab showing on your Ribbon? If
not, click the Office Button (large round icon in upper left corner of
Excel), click the Excel Options button at the bottom of the dialog that
displays, click the Popular item in the left-hand list and put a check mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click the
Developer Tab and then click the Insert icon on the Controls tab, then click
the "More Controls" icon in the bottom right corner of the ActiveX Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the list,
select it and click the OK button and draw the control onto the worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

Well, you have to write VB event code to make it do something in response to
the user's actions. We can try and give you some code to get you started,
but if you are not a programmer, then you will probably be stuck with
whatever code the volunteers here in this newsgroup can write for. Exactly
how did you want it and user to interact?

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks Rick, I have it on my sheet now, but how do I make it work?

"Rick Rothstein" wrote:

Yes. First off, do you have the Developer Tab showing on your Ribbon? If
not, click the Office Button (large round icon in upper left corner of
Excel), click the Excel Options button at the bottom of the dialog that
displays, click the Popular item in the left-hand list and put a check
mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click the
Developer Tab and then click the Insert icon on the Controls tab, then
click
the "More Controls" icon in the bottom right corner of the ActiveX
Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the
list,
select it and click the OK button and draw the control onto the
worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

Thanks again Rick, I just want to be able to open up the sheet and pick a
date that will print on the form.

"Rick Rothstein" wrote:

Well, you have to write VB event code to make it do something in response to
the user's actions. We can try and give you some code to get you started,
but if you are not a programmer, then you will probably be stuck with
whatever code the volunteers here in this newsgroup can write for. Exactly
how did you want it and user to interact?

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks Rick, I have it on my sheet now, but how do I make it work?

"Rick Rothstein" wrote:

Yes. First off, do you have the Developer Tab showing on your Ribbon? If
not, click the Office Button (large round icon in upper left corner of
Excel), click the Excel Options button at the bottom of the dialog that
displays, click the Popular item in the left-hand list and put a check
mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click the
Developer Tab and then click the Insert icon on the Controls tab, then
click
the "More Controls" icon in the bottom right corner of the ActiveX
Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the
list,
select it and click the OK button and draw the control onto the
worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

You didn't say *where* on the form (by which I'm assuming you mean worksheet
that is set up to look like a "form" and not a VB UserForm), so I'll assume
you want to put the date into the active cell. Right click the tab at the
bottom of the worksheet that you placed the DatePicker control on, select
View Code from the popup menu that appeared, and then copy/paste this code
into the code window that appeared...

'********** START OF CODE **********
Private Sub DTPicker21_CloseUp()
ActiveCell.Value = DTPicker21.Value
End Sub

Private Sub DTPicker21_KeyUp(KeyCode As Integer, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Value = DTPicker21.Value
End Sub
'********** END OF CODE **********

Now, go back to the worksheet. First, if the "Design Mode" icon on the
Controls panel of the Developers tab is not highlighted, click it to
highlight it and then just move the DatePicker control around and then place
it where you want it (I found the "moving around" part necessary, otherwise
the control is placed as a duplicate in the upper left corner of the grid).
Next, turn "Design Mode" off by clicking the icon again. That's it. Select a
cell and then choose a date from the drop down calendar and it will go into
the cell you selected. If you choose to change the date in the control via
the keyboard (that is, without using the drop down), then you must press the
Enter key when you are done in order to place the date you changed the
control to via the keyboard into the active cell.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks again Rick, I just want to be able to open up the sheet and pick a
date that will print on the form.

"Rick Rothstein" wrote:

Well, you have to write VB event code to make it do something in response
to
the user's actions. We can try and give you some code to get you started,
but if you are not a programmer, then you will probably be stuck with
whatever code the volunteers here in this newsgroup can write for.
Exactly
how did you want it and user to interact?

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks Rick, I have it on my sheet now, but how do I make it work?

"Rick Rothstein" wrote:

Yes. First off, do you have the Developer Tab showing on your Ribbon?
If
not, click the Office Button (large round icon in upper left corner of
Excel), click the Excel Options button at the bottom of the dialog
that
displays, click the Popular item in the left-hand list and put a check
mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click
the
Developer Tab and then click the Insert icon on the Controls tab, then
click
the "More Controls" icon in the bottom right corner of the ActiveX
Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the
list,
select it and click the OK button and draw the control onto the
worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

By the way, it is possible that your DatePicker control has a different Name
than the one I added to my worksheet. If that is the case, you will have to
change the Name I used (DTPicker21) to your control's name everywhere it
appears in my code (or, alternately, you can just change your control's Name
to DTPicker21 in the Properties windows and leave my code as is).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You didn't say *where* on the form (by which I'm assuming you mean
worksheet that is set up to look like a "form" and not a VB UserForm), so
I'll assume you want to put the date into the active cell. Right click the
tab at the bottom of the worksheet that you placed the DatePicker control
on, select View Code from the popup menu that appeared, and then
copy/paste this code into the code window that appeared...

'********** START OF CODE **********
Private Sub DTPicker21_CloseUp()
ActiveCell.Value = DTPicker21.Value
End Sub

Private Sub DTPicker21_KeyUp(KeyCode As Integer, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Value = DTPicker21.Value
End Sub
'********** END OF CODE **********

Now, go back to the worksheet. First, if the "Design Mode" icon on the
Controls panel of the Developers tab is not highlighted, click it to
highlight it and then just move the DatePicker control around and then
place it where you want it (I found the "moving around" part necessary,
otherwise the control is placed as a duplicate in the upper left corner of
the grid). Next, turn "Design Mode" off by clicking the icon again. That's
it. Select a cell and then choose a date from the drop down calendar and
it will go into the cell you selected. If you choose to change the date in
the control via the keyboard (that is, without using the drop down), then
you must press the Enter key when you are done in order to place the date
you changed the control to via the keyboard into the active cell.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks again Rick, I just want to be able to open up the sheet and pick a
date that will print on the form.

"Rick Rothstein" wrote:

Well, you have to write VB event code to make it do something in
response to
the user's actions. We can try and give you some code to get you
started,
but if you are not a programmer, then you will probably be stuck with
whatever code the volunteers here in this newsgroup can write for.
Exactly
how did you want it and user to interact?

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks Rick, I have it on my sheet now, but how do I make it work?

"Rick Rothstein" wrote:

Yes. First off, do you have the Developer Tab showing on your Ribbon?
If
not, click the Office Button (large round icon in upper left corner
of
Excel), click the Excel Options button at the bottom of the dialog
that
displays, click the Popular item in the left-hand list and put a
check
mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click
the
Developer Tab and then click the Insert icon on the Controls tab,
then
click
the "More Controls" icon in the bottom right corner of the ActiveX
Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the
list,
select it and click the OK button and draw the control onto the
worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?

Date Picker
 

This is a great tip! I have a follow-up question: If I use this for 2 fields,
let's say "Start Date" and "End Date", can Excel read the date range for
different formulas/functions. If for example, I wanted to pull data based on
these ranges for VLookup, would that read it? Or does the control essentially
exist "on top" of the Excel data like a graphic?

Thanks!!

"Rick Rothstein" wrote:

By the way, it is possible that your DatePicker control has a different Name
than the one I added to my worksheet. If that is the case, you will have to
change the Name I used (DTPicker21) to your control's name everywhere it
appears in my code (or, alternately, you can just change your control's Name
to DTPicker21 in the Properties windows and leave my code as is).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You didn't say *where* on the form (by which I'm assuming you mean
worksheet that is set up to look like a "form" and not a VB UserForm), so
I'll assume you want to put the date into the active cell. Right click the
tab at the bottom of the worksheet that you placed the DatePicker control
on, select View Code from the popup menu that appeared, and then
copy/paste this code into the code window that appeared...

'********** START OF CODE **********
Private Sub DTPicker21_CloseUp()
ActiveCell.Value = DTPicker21.Value
End Sub

Private Sub DTPicker21_KeyUp(KeyCode As Integer, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Value = DTPicker21.Value
End Sub
'********** END OF CODE **********

Now, go back to the worksheet. First, if the "Design Mode" icon on the
Controls panel of the Developers tab is not highlighted, click it to
highlight it and then just move the DatePicker control around and then
place it where you want it (I found the "moving around" part necessary,
otherwise the control is placed as a duplicate in the upper left corner of
the grid). Next, turn "Design Mode" off by clicking the icon again. That's
it. Select a cell and then choose a date from the drop down calendar and
it will go into the cell you selected. If you choose to change the date in
the control via the keyboard (that is, without using the drop down), then
you must press the Enter key when you are done in order to place the date
you changed the control to via the keyboard into the active cell.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks again Rick, I just want to be able to open up the sheet and pick a
date that will print on the form.

"Rick Rothstein" wrote:

Well, you have to write VB event code to make it do something in
response to
the user's actions. We can try and give you some code to get you
started,
but if you are not a programmer, then you will probably be stuck with
whatever code the volunteers here in this newsgroup can write for.
Exactly
how did you want it and user to interact?

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Thanks Rick, I have it on my sheet now, but how do I make it work?

"Rick Rothstein" wrote:

Yes. First off, do you have the Developer Tab showing on your Ribbon?
If
not, click the Office Button (large round icon in upper left corner
of
Excel), click the Excel Options button at the bottom of the dialog
that
displays, click the Popular item in the left-hand list and put a
check
mark
in the "Show Developer tab in the Ribbon" check box. Okay, now click
the
Developer Tab and then click the Insert icon on the Controls tab,
then
click
the "More Controls" icon in the bottom right corner of the ActiveX
Controls
section and find "Microsoft Date and Time Picker Control 6.0" in the
list,
select it and click the OK button and draw the control onto the
worksheet.

--
Rick (MVP - Excel)


"Canon" wrote in message
...
Excel 2007
Can I insert a "Date Picker" in Excel like you can in Word?