Date Picker

  • Thread starter Thread starter Canon
  • Start date Start date
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.
 
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?
 
Thanks again Rick, I just want to be able to open up the sheet and pick a
date that will print on the form.
 
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.
 
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).
 
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!!
 
Back
Top