Solved

Date picker

Posted on 2016-11-01
12
44 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 45

Expert Comment

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

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 45

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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 45

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 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

17 Experts available now in Live!

Get 1:1 Help Now