Excel 2010 Date Picker

Topic

New Reply

Dear loungers,

I am adding simple data validation to a column want the user to be able to pick a date from a claendar. i’ve found so many referneces and solutions I can’t tell the best thing.

Advice please……………………… liz

Reply | Quote

Replies

Lizat,

Data validation will only check to see if the entry is a valid date according to the parameters you set. However, it will not provide them with a mechanism for entering the date. There is an active X element within VB accessible to Excel called the DatePicker. I just used it in some code I wrote for another lounge member. You can access it directly to the form or from VB. Go to the Developer tab in Excel 2010. If the developer tab not visible, go File>Options then add it to the ribbon. Click the insert menu and add it by clicking more controls. Scroll to the Microsoft Date and Time Picker Control, version 6.0, click it and then OK. Your pointer will be a cross hair. Drag the cursor on the sheet to draw the control. Alternately, you can do this in VB. Once VB open, create a userform then add the DatePicker to it using the same technique. To access the date Picker when someone clicks on a certain cell, you must add some simple code and the DatePicker will open and the user selects a date. It then places the date in the cell or wherever you desire. If you post a copy of your spreadsheet, I will set you up with the control. Here is what it looks like when open:

32282-DatePicker32285-MoreControls

The Code:
Private SubCommandButton1_Click()
‘OKButton gives the active cell the sected date then closes form
ActiveCell.Value= DTPicker1.Value
SetDate.Hide
End Sub

_______________________________________________________________________

Private SubCommandButton2_Click()
‘Clearbutton sets the actie cell to blank and closes form
ActiveCell.Value= “”
SetDate.Hide
End Sub
______________________________________________________________________________

Private SubUserForm_Activate()
‘Setsthe default date to current date for the DatePicker when the form opens
DTPicker1.Value= Date
End Sub

HTH,
Maud

Reply | Quote

With Excel 2010 this does not work. A similar Active X controll is available in the professional version only. The problem is that there are noe forward, backward or sideways compatability. So even though I can create a workbook with this function, when I mail this workbook to any person who does not have Excell 2010 professional it will not work. I have found VB code that can be embedded in the workbook as a work-around, but my problem is that with this module, I can not select a cell and set the property to display the date picker for that cell only.

Any ideas or alternative method to accomplish this:

1. Create a workbook in Excel 2010 that includes specific cells that the data can only be entered into by datepicker.
2. The datepicker has to be a workbook module.
3. The datepicker has to offer compatibility with the full range of excell version.

Thanks.

Ensemble

WHY?

Reply | Quote

Aha, thank you Maudibe!

Reply | Quote

With Excel 2010 this does not work.

Date Picker is specific to Excel 2010. I do not have the Pro version and I am able to use this control flawlessly. The OP states he/she has 2010 as well. In Versions 2003-2007 MS calendar Control (MSCAL.OCX) control was used and therefore, using the Date Picker control from 2010 is not backwards compatible. I have read of installing the MSCAL.OCX active X control in 2010 but the results seem mixed. Perhaps, building a custom control might be an alternative. Another option might be to use conditional statements to launch the control based on the version running. Will give more thought to it and see if I can come up with something

Reply | Quote

Ensemble,

Here is a routine that will conditionally run code based on the version of Excel being used. This may be a way to do what you are looking for. Based on the same principal where web sites check the browser version then display content based on the returned result.

Code:

Sub FindVersion()
    If Application.Version = “14.0” Then
        ‘ 2010: Code goes here
    ElseIf Application.Version = “12.0” Then
        ‘2007: Code goes here
    ElseIf Application.Version = “11.0” Then
        ‘2003: Code goes here
    End If
End Sub

Reply | Quote

I found this, it does not use ActiveX at all and fits in with what I need, my only problem is that using it as is, the funcionality is available in the whole sheet, I want it restricted to the range A15:E34.

http://www.ozgrid.com/forum/showthread/?t=142603

Reply | Quote

Hi

To restrict the date picker to the range [a15:e34] you just need to test for this range as follows:

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Intersect(Target, [a15:e34]) Is Nothing Then Exit Sub    'adjust range to suit

Dim myDate As Date

Set clsCal = New ClsCalendar

FormPicker.Show

myDate = clsCal.SelectedDate

If myDate > 0 Then  'Check to see if it was cancelled
    Target.Value = clsCal.SelectedDate
End If

Finally:
Set clsCal = Nothing
Cancel = True


End Sub

zeddy

Reply | Quote