Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date picker

Posted on 2016-11-01
12
Medium Priority
?
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 49

Expert Comment

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

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

609 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