Solved

Date picker

Posted on 2016-11-01
12
51 Views
Last Modified: 2016-11-03
How do i get this code to initiate on a double click on the in the cell. At the moment the date picker comes up immediately the cell is selected. This is a little inconvenient.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     'check cells for desired format to trigger the calendarfrm.show routine
     'otherwise exit the sub
    Dim DateFormats, DF
    DateFormats = Array("d/mmmm/yy;@", "d mmmm yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next
End Sub


thank you
0
Comment
Question by:Sam Coombes
  • 7
  • 5
12 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41869257
Move the code to the Before_DoubleClick code for the sheet.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41869259
In other words

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'check cells for desired format to trigger the calendarfrm.show routine
     'otherwise exit the sub
    Dim DateFormats, DF
    DateFormats = Array("d/mmmm/yy;@", "d mmmm yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next
End Sub

Open in new window

1
 

Author Comment

by:Sam Coombes
ID: 41869275
thats what I thought but I still cant get that to work
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41869282
Can you attach your workbook or a sample workbook?
0
 

Author Comment

by:Sam Coombes
ID: 41870348
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41870527
OK, you had two problems.
1) The code only works (even in the Worksheet_SelectionChange event) if a field has a date format of either "d/mmmm/yy;@" or "d mmmm yyyy". I couldn't find one that did so I changed cell J7.
2) in Tools->References one of the references was marked as "MISSING" and I corrected that by unchecking it

I then copied the code to the Worksheet_BeforeDoubleClick event and now double-clicking that cell brings up the calendar. Note that there are ways to have the calendar pop up on double-click in that column or any other specific column(s) without them having to have any specific date format. Let me know if that's something you'd like.
28980347.xlsm
1
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Sam Coombes
ID: 41871723
That sounds great but the file you sent me doesn't do that yet.

Thank You
0
 

Author Comment

by:Sam Coombes
ID: 41871727
Correction the Calendar function works amazing thank you so much
0
 

Author Comment

by:Sam Coombes
ID: 41871901
The Date picker still keeps showing False on the sheet
0
 

Author Comment

by:Sam Coombes
ID: 41872308
Works perfect amazing thank you
0
 

Author Closing Comment

by:Sam Coombes
ID: 41872309
Thank you very much
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41872448
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now