Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Excel 2010 Date Picker

    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

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,218
    Thanks
    46
    Thanked 234 Times in 215 Posts

    DatePicker Active X control

    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:

    DatePicker.jpgMoreControls.jpg

    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
    Last edited by Maudibe; 2012-11-04 at 05:19.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    lizat (2012-11-05)

  4. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Aha, thank you Maudibe!

  5. #4
    New Lounger Ensemble's Avatar
    Join Date
    Apr 2013
    Location
    Centurion
    Posts
    23
    Thanks
    9
    Thanked 1 Time in 1 Post
    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?

  6. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,218
    Thanks
    46
    Thanked 234 Times in 215 Posts
    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

  7. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,218
    Thanks
    46
    Thanked 234 Times in 215 Posts
    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
    Last edited by Maudibe; 2013-04-11 at 18:34.

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Lexi (2014-02-08)

  9. #7
    New Lounger Ensemble's Avatar
    Join Date
    Apr 2013
    Location
    Centurion
    Posts
    23
    Thanks
    9
    Thanked 1 Time in 1 Post
    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.php?t=142603

  10. #8
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •