Solved

Date picker

Posted on 2016-11-01
12
55 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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
 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 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

10 Experts available now in Live!

Get 1:1 Help Now